Catalogo alp_cat_ar_clientes

1. Modulo

Cuentas por Cobrar (AR)

---

2. Descripción

Maestro general de clientes, cuentas, direcciones y estados de cuenta comerciales.

---

3. Columnas

#	Columna	Tipo / Origen	Descripción
1	appl_source	String	Sistema fuente (Default: 'AR').
2	client_ar_id	Long	Identificador funcional único del registro de cliente.
3	party_id	Long	ID único de la entidad (HZ_PARTIES).
4	identificador_registro	String	Número de registro de la entidad (party_number).
5	nombre	String	Razón social o nombre comercial.
6	tipo_cliente	String	Clasificación del tipo de cliente.
7	numero_identificacion_contribuyente	String	RFC / ID Fiscal.
8	regimen_fiscal	String	Código de régimen fiscal.
9	desc_regimen_fiscal	String	Descripción del régimen fiscal.
10	tipo_persona_fisica	String	Código de tipo de persona física.
11	desc_tipo_persona_fisica	String	Descripción del tipo de persona física.
12	party_osr_id	Long	ID referencia del sistema origen (Party).
13	party_osr	String	Código de referencia del sistema origen (Party).
14	cust_account_id	Long	ID interno de la cuenta (CUST_ACCOUNTS).
15	numero_cuenta_cliente	String	Número de cuenta contable.
16	nombre_cuenta_cliente	String	Nombre de la cuenta de cliente.
17	clase_cuenta	String	Código de clase de crédito.
18	desc_clase_cuenta	String	Descripción de la clase de crédito.
19	canal_tipo_cuenta	String	Clasificación del canal de venta.
20	desc_canal_tipo_cuenta	String	Descripción del canal.
21	canal_400_tipo_cuenta	String	Tag para integración con AS400.
22	grupo_comercial	String	Código del grupo comercial.
23	desc_grupo_comercial	String	Descripción del grupo comercial.
24	grupo_comercial_400	String	Tag para integración con AS400.
25	unidad_facturacion	String	Código de unidad de facturación.
26	desc_unidad_facturacion	String	Descripción de unidad de facturación.
27	forma_pago	String	Código de forma de pago.
28	desc_forma_pago	String	Descripción de forma de pago.
29	tipo_factura	String	Código de tipo de factura.
30	desc_tipo_factura	String	Descripción de tipo de factura.
31	metodo_pago	String	Código de método de pago.
32	desc_metodo_pago	String	Descripción de método de pago.
33	dias_gracia	String	Días de gracia otorgados.
34	codigo_bancario	String	Código bancario de la cuenta.
35	subcanal	String	Código de subcanal.
36	desc_subcanal	String	Descripción del subcanal.
37	zona_cobranza	String	Código de zona de cobranza.
38	desc_zona_cobranza	String	Descripción de la zona de cobranza.
39	dias_pago	String	Días para el pago.
40	dias_recepcion	String	Días para la recepción.
41	grupo_concentrador	String	Código del grupo concentrador.
42	desc_grupo_concentrador	String	Descripción del grupo concentrador.
43	no_cliente_factura_concentrada	String	ID de cliente para facturación concentrada.
44	agrupacion_ibp	String	Código de agrupación IBP.
45	desc_agrupacion_ibp	String	Descripción de la agrupación IBP.
46	agrupacion_ibp_400	String	Tag para integración con AS400.
47	timbrado_individual	String	Flag para timbrado individual.
48	uso_cfdi	String	Código de uso CFDI.
49	desc_uso_cfdi	String	Descripción del uso CFDI.
50	numero_proveedor	String	Número de proveedor asociado.
51	incoterm	String	Incoterm comercial.
52	tipo_addenda	String	Código de tipo de addenda.
53	desc_tipo_addenda	String	Descripción de tipo de addenda.
54	macro_canal	String	Código de macro canal.
55	desc_macro_canal	String	Descripción de macro canal.
56	giro_cliente	String	Código de giro del cliente.
57	desc_giro_cliente	String	Descripción de giro del cliente.
58	genera_addenda	String	Flag para generación de addenda.
59	referencia_cie	String	Referencia CIE bancaria.
60	referencia_cuenta	String	Referencia de cuenta general.
61	account_osr_id	Long	ID referencia origen (Cuenta).
62	account_osr	String	Código referencia origen (Cuenta).
63	party_site_id	Long	ID del sitio del cliente.
64	numero_sitio	String	Número de identificación del sitio.
65	nombre_sitio	String	Nombre asignado al sitio.
66	direccion_primaria	String	Flag de dirección principal.
67	party_site_osr_id	Long	ID referencia origen (Sitio).
68	party_site_osr	String	Código referencia origen (Sitio).
69	location_id	Long	ID interno de localización.
70	pais	String	Código de país.
71	codigo_postal	String	Código postal.
72	calle	String	Dirección (calle).
73	numero_exterior	String	Número exterior.
74	numero_interior	String	Número interior.
75	colonia	String	Colonia.
76	alcaldia_municipio	String	Alcaldía o municipio.
77	estado	String	Estado o provincia.
78	entre_calle_1	String	Referencia vial 1.
79	entre_calle_2	String	Referencia vial 2.
80	latitud	String	Latitud geográfica.
81	longitud	String	Longitud geográfica.
82	codigo_gln	String	Código GLN.
83	location_osr_id	Long	ID referencia origen (Localización).
84	location_osr	String	Código referencia origen (Localización).
85	cust_acct_site_id	Long	ID de relación cuenta-sitio.
86	bill_to_flag	String	Flag de facturación.
87	ship_to_flag	String	Flag de envío.
88	plaza_distribucion	String	Código de plaza.
89	desc_plaza_distribucion	String	Descripción de plaza.
90	plaza_distribucion_400	String	Tag AS400.
91	factura_anticipada	String	Flag para factura anticipada.
92	cedis_asignado	String	ID del CEDIS asignado.
93	desc_cedis_asignado	String	Descripción del CEDIS.
94	cedis_asignado_400	String	Tag AS400.
95	no_sucursal_factura_concentrada	String	Sucursal de facturación.
96	codigo_exportacion	String	Código de exportación.
97	zona_nielsen	String	Zona Nielsen.
98	desc_zona_nielsen	String	Descripción de zona Nielsen.
99	regional_responsable	String	Regional responsable.
100	desc_regional_responsable	String	Descripción de regional.
101	tipo_entrega	String	Tipo de entrega.
102	desc_tipo_entrega	String	Descripción de entrega.
103	segmentacion_categoria_externa	String	Categoría externa.
104	desc_segmentacion_categoria_externa	String	Descripción de categoría externa.
105	categoria_interna	String	Categoría interna.
106	desc_categoria_interna	String	Descripción de categoría interna.
107	cuenta_bancaria	String	Cuenta bancaria asociada.
108	metodo_comercializacion	String	Método de comercialización.
109	desc_metodo_comercializacion	String	Descripción método comercial.
110	codigo_sucursal	String	Código de sucursal.
111	identificador_edi	String	ID para EDI.
112	zona_precios	String	Zona de precios.
113	desc_zona_precios	String	Descripción zona de precios.
114	guid_chep	String	GUID de CHEP.
115	inspeccion_calidad	String	Flag inspección calidad.
116	numero_tienda_retek	String	ID tienda Retek.
117	perfil_cliente	String	Perfil del cliente.
118	tipo_pedido_cliente	String	Tipo de pedido.
119	referencia_sucursal	String	Referencia de sucursal.
120	account_site_osr_id	Long	ID referencia origen (Sitio-Cuenta).
121	account_site_osr	String	Código referencia origen (Sitio-Cuenta).
122	party_site_use_id	Long	ID de uso de sitio.
123	primario_por_tipo	String	Flag uso primario.
124	party_site_use_osr_id	Long	ID referencia origen (Uso Sitio).
125	party_site_use_osr	String	Código referencia origen (Uso Sitio).
126	site_use_id	Long	ID uso de sitio cuenta.
127	sitio_cuenta_primario	String	Flag sitio cuenta primario.
128	uso_sitio_cuenta	String	Tipo de uso.
129	sitio	String	Código sitio.
130	account_site_use_osr_id	Long	ID referencia origen (Uso Sitio-Cuenta).
131	account_site_use_osr	String	Código referencia origen (Uso Sitio-Cuenta).
132	terminos_pago	String	Términos de pago.
133	desc_terminos_pago	String	Descripción términos de pago.
134	cedis_asignado_as400	String	Tag CEDIS AS400.
135	emails_facturacion	String	Son los correos para facturación recuperados del modulo de CDM.

---

4. Querys

Databricks DEV

with  alp_party_site_use as
        (   select  row_number()
                    over (  partition by    hpsu.partysiteid
                                    ,       hpsu.siteusetype
                            order by    hosr5.creationdate)     reg
                ,   hpsu.partysiteid                            party_site_id
                ,   hpsu.siteusetype                            site_use_type
                ,   hpsu.partysiteuseid                         party_site_use_id
                ,   hpsu.primarypertype                         primario_por_tipo
                ,   hosr5.origsystemrefid                       party_site_use_osr_id
                ,   hosr5.origsystemreference                   party_site_use_osr
            from    cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partysiteuseextractpvo               hpsu
                join  cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo  hosr5
                    on      hpsu.partysiteuseid         =       hosr5.ownertableid
                        and hosr5.origsystemreference   like    'PSU%')
, alp_cat_fnd_lookups as
    ( select  flv.lookuptype        type
          ,   flv.lookupcode        code
          ,   flvt.meaning    
          ,   flv.setid             set_id   
          ,   flv.tag
          ,   flv.viewapplicationid view_appl_id
          ,   flv.enabledflag       enabled
          ,   flv.startdateactive   start_date
          ,   flv.enddateactive     end_date
      from    cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluesextractpvo   flv
        join  cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo flvt
          on    flv.lookuptype        = flvt.lookuptype
            and flv.lookupcode        = flvt.lookupcode
            and flv.viewapplicationid = flvt.viewapplicationid
                and   flvt.language         = 'E')
, alp_niveles_cdm as
    ( select  party_id
          ,   extn_attribute_char008  nivel
          ,   party_id                contact_party_id
      from    cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles
      where   extn_attribute_char008  = 'ORGANIZACION'
      union all
      select  CAST(extn_attribute_char003 as double)    party_id  
          ,   extn_attribute_char008                    nivel
          ,   party_id                                  contact_party_id
      from    cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles
      where   extn_attribute_char008  = 'CUENTA'
      union all
      select  CAST(c.extn_attribute_char003 as double)  party_id 
          ,   s.extn_attribute_char008                  nivel
          ,   s.party_id                                contact_party_id
      from    cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles  c
        join  cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles  s
          on  c.party_id                = s.extn_attribute_number001
      where   s.extn_attribute_char008  = 'SUCURSAL')
,   alp_contactos_facturacion as
    ( select  anc.party_id
          ,   hcp.email_address     email
          ,   MAX(hr.creation_date) creacion
      from    alp_niveles_cdm                                                 anc
        join  cat_master_oracle_fusion.sch_gold_layer.hz_relationships    hr
          on  anc.contact_party_id          =       hr.subject_id
            and hr.subject_type             =       'ORGANIZATION'
            and hr.object_type              =       'PERSON'
            and hr.object_table_name        =       'HZ_PARTIES'
            and hr.relationship_code        =       'CONTACT'
            and current_date()              between hr.start_date
                                            and     hr.end_date 
        join  cat_master_oracle_fusion.sch_gold_layer.hz_contact_points   hcp
          on  hr.object_id                  =       hcp.owner_table_id
            and hcp.contact_point_type      =       'EMAIL'
            and hcp.owner_table_name        =       'HZ_PARTIES'
            and hcp.orig_system_reference   is not  null
            and current_date()              between hcp.start_date
                                            and     hcp.end_date
        join  cat_master_oracle_fusion.sch_gold_layer.hz_person_profiles  hpp
          on  hr.object_id                  =       hpp.party_id
            and hpp.extn_attribute_char004  =       'FACTURA'
      group by  anc.party_id
          ,     hcp.email_address)
,   alp_limit_contact_facturacion as
    ( select  party_id
          ,   email
          ,   ROW_NUMBER()
              over( partition by    party_id
                    order by    creacion)       reg
      from    alp_contactos_facturacion)
,   alp_contact_facturacion as
    ( select  party_id
          ,   array_join(sort_array(collect_list(email)),', ') as emails
      from    alp_limit_contact_facturacion
      where   reg <= 5
      group by party_id)  
select  'AR'                        appl_source
    -- parties
,   CONCAT_WS('|', hp.partynumber, hca.accountnumber , hcsu.location)    client_ar_id
,   hp.partyid                  party_id
,   hp.partynumber              identificador_registro
,   hp.partyname                nombre
,   hp.partytype                tipo_cliente
,   hp.jgzzfiscalcode           numero_identificacion_contribuyente
,   op.attribute1               regimen_fiscal
,   flv1.meaning                desc_regimen_fiscal
,   op.attribute2               tipo_persona_fisica
,   flv2.meaning                desc_tipo_persona_fisica
--  parties_references
,   hosr1.origsystemrefid       party_osr_id
,   hosr1.origsystemreference   party_osr
--  accounts
,   hca.custaccountid           cust_account_id
,   hca.accountnumber           numero_cuenta_cliente
,   hca.accountname             nombre_cuenta_cliente
,   hca.customerclasscode       clase_cuenta
,   flv3.meaning                desc_clase_cuenta
,   hca.customertype            canal_tipo_cuenta
,   flv4.meaning                desc_canal_tipo_cuenta
,   flv4.tag                    canal_400_tipo_cuenta
,   hca.attribute1              grupo_comercial
,   flv5.meaning                desc_grupo_comercial
,   flv5.tag                    grupo_comercial_400
,   hca.attribute2              unidad_facturacion
,   flv6.meaning                desc_unidad_facturacion
,   hca.attribute3              forma_pago
,   flv7.meaning                desc_forma_pago
,   hca.attribute4              tipo_factura
,   flv8.meaning                desc_tipo_factura
,   hca.attribute5              metodo_pago
,   flv9.meaning                desc_metodo_pago
,   hca.attribute6              dias_gracia
,   hca.attribute7              codigo_bancario
,   hca.attribute8              subcanal
,   flv10.meaning               desc_subcanal
,   hca.attribute9              zona_cobranza
,   flv11.meaning               desc_zona_cobranza
,   hca.attribute10             dias_pago
,   hca.attribute11             dias_recepcion
,   hca.attribute12             grupo_concentrador
,   flv12.meaning               desc_grupo_concentrador
,   hca.attribute13             no_cliente_factura_concentrada
,   hca.attribute14             agrupacion_ibp
,   flv13.meaning               desc_agrupacion_ibp
,   flv13.tag                   agrupacion_ibp_400
,   hca.attribute15             timbrado_individual
,   hca.attribute16             uso_cfdi
,   flv14.meaning               desc_uso_cfdi
,   hca.attribute17             numero_proveedor
,   hca.attribute18             incoterm
,   hca.attribute19             tipo_addenda
,   flv26.meaning               desc_tipo_addenda
,   hca.attribute20             macro_canal
,   flv15.meaning               desc_macro_canal
,   hca.attribute21             giro_cliente
,   flv16.meaning               desc_giro_cliente
,   hca.attribute22             genera_addenda
,   hca.attribute23             referencia_cie
,   hca.attribute24             referencia_cuenta
--  account_references
,   hosr2.origsystemrefid       account_osr_id
,   hosr2.origsystemreference   account_osr
--  party_sites
,   hps.partysiteid             party_site_id
,   hps.partysitenumber         numero_sitio
,   hps.partysitename           nombre_sitio
,   hps.identifyingaddressflag  direccion_primaria
--  party_sites_refernces
,   hosr3.origsystemrefid       party_site_osr_id
,   hosr3.origsystemreference   party_site_osr
--  hz_locations
,   hl.locationid               location_id
,   hl.country                  pais
,   hl.postalcode               codigo_postal
,   hl.address1                 calle
,   hl.address2                 numero_exterior
,   hl.address3                 numero_interior
,   hl.city                     colonia
,   hl.county                   alcaldia_municipio
,   hl.state                    estado
,   hl.addrelementattribute1    entre_calle_1
,   hl.addrelementattribute2    entre_calle_2
,   hl.addrelementattribute3    latitud
,   hl.addrelementattribute4    longitud
,   hl.addrelementattribute5    codigo_gln   
--  locations_refrences
,   hosr7.origsystemrefid       location_osr_id
,   hosr7.origsystemreference   location_osr
--  account_sites
,   hcasa.custacctsiteid        cust_acct_site_id
,   hcasa.billtoflag            bill_to_flag
,   hcasa.shiptoflag            ship_to_flag
,   hcasa.attribute1            plaza_distribucion
,   flv17.meaning               desc_plaza_distribucion
,   flv17.tag                   plaza_distribucion_400
,   hcasa.attribute2            factura_anticipada
,   hcasa.attribute3            cedis_asignado
,   flv18.meaning               desc_cedis_asignado
,   flv18.tag                   cedis_asignado_400
,   hcasa.attribute4            no_sucursal_factura_concentrada
,   hcasa.attribute5            codigo_exportacion
,   hcasa.attribute6            zona_nielsen
,   flv19.meaning               desc_zona_nielsen
,   hcasa.attribute7            regional_responsable
,   flv20.meaning               desc_regional_responsable
,   hcasa.attribute8            tipo_entrega
,   flv21.meaning               desc_tipo_entrega
,   hcasa.attribute9            segmentacion_categoria_externa
,   flv22.meaning               desc_segmentacion_categoria_externa
,   hcasa.attribute10           categoria_interna
,   flv23.meaning               desc_categoria_interna
,   hcasa.attribute11           cuenta_bancaria
,   hcasa.attribute12           metodo_comercializacion
,   flv24.meaning               desc_metodo_comercializacion
,   hcasa.attribute13           codigo_sucursal
,   hcasa.attribute14           identificador_edi
,   hcasa.attribute15           zona_precios
,   flv25.meaning               desc_zona_precios
,   hcasa.attribute16           guid_chep
,   hcasa.attribute17           inspeccion_calidad
,   hcasa.attribute18           numero_tienda_retek
,   hcasa.attribute19           perfil_cliente
,   hcasa.attribute20           tipo_pedido_cliente
,   hcasa.attribute21           referencia_sucursal
--  account_site_references
,   hosr4.origsystemrefid       account_site_osr_id
,   hosr4.origsystemreference   account_site_osr
--  party_site_uses
,   hpsu.party_site_use_id
,   hpsu.primario_por_tipo
--  party_site_uses_references
,   hpsu.party_site_use_osr_id
,   hpsu.party_site_use_osr
--  account_site_uses
,   hcsu.siteuseid              site_use_id
,   hcsu.primaryflag            sitio_cuenta_primario
,   hcsu.siteusecode            uso_sitio_cuenta
,   hcsu.location               sitio
--  account_site_uses_references
,   hosr6.origsystemrefid       account_site_use_osr_id
,   hosr6.origsystemreference   account_site_use_osr
--  terminos de pago    
,   rtt.ratermtlname            terminos_pago
,   rtt.ratermtldescription     desc_terminos_pago
,   flv27.tag                   cedis_asignado_as400
,   acf.emails                  email_facturacion
from            cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partyextractpvo                    hp
join        cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles                      op
    on  hp.partyid  =   op.party_id
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountextractpvo          hca
    on  hp.partyid  =   hca.partyid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountsiteextractpvo      hcasa
    on  hca.custaccountid   =   hcasa.custaccountid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partysiteextractpvo                hps
    on  hcasa.partysiteid   =   hps.partysiteid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_locationextractpvo                 hl
    on  hps.locationid  =   hl.locationid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeracctsiteuseextractpvo      hcsu
    on      hcasa.custacctsiteid    =   hcsu.custacctsiteid
join        alp_party_site_use                                                                        hpsu
    on      hps.partysiteid     =   hpsu.party_site_id
        and hcsu.siteusecode    =   hpsu.site_use_type
        and hpsu.reg            =   1
left join   cat_master_oracle_fusion.sch_gold_layer.fscm_fin_ar_paymenttermtlextractpvo           rtt
    on      hcsu.paymenttermid      =   rtt.ratermtltermid
        and rtt.ratermtllanguage    =   'E'
left join   cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_setidsetspvo                     fasis
    on      hcasa.setid     =   fasis.setid
        and fasis.language  =   'E'
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo  hosr1
    on  hp.partyid  =   hosr1.ownertableid
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo  hosr2
    on      hca.custaccountid       =   hosr2.ownertableid
        and hca.origsystemreference =   hosr2.origsystemreference
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo  hosr3
    on      hps.partysiteid             =       hosr3.ownertableid
        and hosr3.origsystemreference   like    'PS%'
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo  hosr4
    on      hcasa.custacctsiteid        =   hosr4.ownertableid
        and hosr4.origsystemreference   =   'FUSION'
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo  hosr6
    on  hcsu.siteuseid  =   hosr6.ownertableid
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo  hosr7
    on  hl.locationid   =   hosr7.ownertableid
left join   alp_cat_fnd_lookups                                                                       flv1
    on      op.attribute1     = flv1.code
        and flv1.type         = 'ALP_REGIMENFISCAL'
        and flv1.view_appl_id = 0
left join   alp_cat_fnd_lookups                                                                       flv2
    on      op.attribute2     = flv2.code
        and flv2.type         = 'ALP_TIPOFISCAL'    
        and flv2.view_appl_id = 0
left join   alp_cat_fnd_lookups                                                                       flv3
    on      hca.customerclasscode = flv3.code
        and flv3.type             = 'ALP_TIPOCREDITO'
        and flv3.view_appl_id     = 0
left join   alp_cat_fnd_lookups                                                                       flv4
    on      hca.customertype    = flv4.code
        and flv4.type           = 'ALP_CANAL'
        and flv4.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv5
    on      hca.attribute1      = flv5.code
        and flv5.type           = 'ALP_GRUPOCOMERCIAL'
        and flv5.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv6
    on      hca.attribute2      = flv6.code
        and flv6.type           = 'ALP_UNIDADFACTURACION'
        and flv6.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv7
    on      hca.attribute3      = flv7.code
        and flv7.type           = 'ALP_FORMAPAGO'
        and flv7.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv8
    on      hca.attribute4      = flv8.code
        and flv8.type           = 'ALP_TIPOFACTURA'
        and flv8.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv9
    on      hca.attribute5      = flv9.code
        and flv9.type           = 'ALP_METODOPAGO'
        and flv9.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv10
    on      hca.attribute8      = flv10.code
        and flv10.type          = 'ALP_SUBCANAL'  
        and flv10.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv11
    on      hca.attribute9      = flv11.code
        and flv11.type          = 'ALP_ZCOBRANZA'
        and flv11.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv12
    on      hca.attribute12     = flv12.code
        and flv12.type          = 'ALP_CONCENTRADOR'  
        and flv12.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv13
    on      hca.attribute14     = flv13.code
        and flv13.type          = 'ALP_IBP'   
        and flv13.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv14
    on      hca.attribute16     = flv14.code
        and flv14.type          = 'ALP_USOCFDI'   
        and flv14.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv15
    on      hca.attribute20     = flv15.code
        and flv15.type          = 'ALP_MACROCANAL'    
        and flv15.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv16
    on      hca.attribute21     = flv16.code
        and flv16.type          = 'ALP_GIRO'
        and flv16.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv17
    on      hcasa.attribute1    = flv17.code
        and flv17.type          = 'ALP_PLAZADISTRIBUCION'
        and flv17.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv18
    on      hcasa.attribute3    = flv18.code
        and flv18.type          = 'ALP_CEDIS'
        and flv18.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv19
    on      hcasa.attribute6    = flv19.code
        and flv19.type          = 'ALP_ZNIELSEN'  
        and flv19.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv20
    on      hcasa.attribute7    = flv20.code
        and flv20.type          = 'ALP_REGIONALRESP'  
        and flv20.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv21
    on      hcasa.attribute8    = flv21.code
        and flv21.type          = 'ALP_TIPOENTREGA'   
        and flv21.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv22
    on      hcasa.attribute9    = flv22.code
        and flv22.type          = 'ALP_SEGMENTACION'  
        and flv22.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv23
    on      hcasa.attribute10   = flv23.code
        and flv23.type          = 'ALP_CLASINTERNA'   
        and flv23.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv24
    on      hcasa.attribute12   = flv24.code
        and flv24.type          = 'ALP_METODOCOMER'
        and flv24.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv25
    on      hcasa.attribute15   = flv25.code
        and flv25.type          = 'ALP_ZONAPRECIOS'
        and flv25.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv26
    on      hca.attribute19     = flv26.code
        and flv26.type          = 'ALP_TIPOADENDA'
        and flv26.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv27
    on      hcasa.attribute3    = flv27.code
        and flv27.type          = 'ALP_CEDIS_CDM_AS400'
        and flv27.view_appl_id  = 0
left join   alp_contact_facturacion                                                                    acf
    on  hp.partyid  =   acf.party_id
where   1                       =       1
and     hp.partytype            =       'ORGANIZATION'
and     ((fasis.setname         =       'GPLP_SET'
and      hca.customerclasscode  =       'R')
or      (flv18.meaning          is      null
and      hca.customerclasscode  <>      'R')
or      flv18.meaning           like    concat('%',replace(fasis.setname,'_SET',''),'%'))

Databricks UAT

with  alp_party_site_use as
        (   select  row_number()
                    over (  partition by    hpsu.partysiteid
                                    ,       hpsu.siteusetype
                            order by    hosr5.creationdate)     reg
                ,   hpsu.partysiteid                            party_site_id
                ,   hpsu.siteusetype                            site_use_type
                ,   hpsu.partysiteuseid                         party_site_use_id
                ,   hpsu.primarypertype                         primario_por_tipo
                ,   hosr5.origsystemrefid                       party_site_use_osr_id
                ,   hosr5.origsystemreference                   party_site_use_osr
            from    cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partysiteuseextractpvo_stg               hpsu
                join  cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_stg  hosr5
                    on      hpsu.partysiteuseid         =       hosr5.ownertableid
                        and hosr5.origsystemreference   like    'PSU%')
, alp_cat_fnd_lookups as
    ( select  flv.lookuptype        type
          ,   flv.lookupcode        code
          ,   flvt.meaning    
          ,   flv.setid             set_id   
          ,   flv.tag
          ,   flv.viewapplicationid view_appl_id
          ,   flv.enabledflag       enabled
          ,   flv.startdateactive   start_date
          ,   flv.enddateactive     end_date
      from    cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluesextractpvo_stg   flv
        join  cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_stg flvt
          on    flv.lookuptype        = flvt.lookuptype
            and flv.lookupcode        = flvt.lookupcode
            and flv.viewapplicationid = flvt.viewapplicationid
                and   flvt.language         = 'E')
, alp_niveles_cdm as
    ( select  party_id
          ,   extn_attribute_char008  nivel
          ,   party_id                contact_party_id
      from    cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles_stg
      where   extn_attribute_char008  = 'ORGANIZACION'
      union all
      select  CAST(extn_attribute_char003 as double)    party_id  
          ,   extn_attribute_char008                    nivel
          ,   party_id                                  contact_party_id
      from    cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles_stg
      where   extn_attribute_char008  = 'CUENTA'
      union all
      select  CAST(c.extn_attribute_char003 as double)  party_id 
          ,   s.extn_attribute_char008                  nivel
          ,   s.party_id                                contact_party_id
      from    cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles_stg  c
        join  cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles_stg  s
          on  c.party_id                = s.extn_attribute_number001
      where   s.extn_attribute_char008  = 'SUCURSAL')
,   alp_contactos_facturacion as
    ( select  anc.party_id
          ,   hcp.email_address     email
          ,   MAX(hr.creation_date) creacion
      from    alp_niveles_cdm                                                 anc
        join  cat_master_oracle_fusion.sch_gold_layer.hz_relationships_stg    hr
          on  anc.contact_party_id          =       hr.subject_id
            and hr.subject_type             =       'ORGANIZATION'
            and hr.object_type              =       'PERSON'
            and hr.object_table_name        =       'HZ_PARTIES'
            and hr.relationship_code        =       'CONTACT'
            and current_date()              between hr.start_date
                                            and     hr.end_date 
        join  cat_master_oracle_fusion.sch_gold_layer.hz_contact_points_stg   hcp
          on  hr.object_id                  =       hcp.owner_table_id
            and hcp.contact_point_type      =       'EMAIL'
            and hcp.owner_table_name        =       'HZ_PARTIES'
            and hcp.orig_system_reference   is not  null
            and current_date()              between hcp.start_date
                                            and     hcp.end_date
        join  cat_master_oracle_fusion.sch_gold_layer.hz_person_profiles_stg  hpp
          on  hr.object_id                  =       hpp.party_id
            and hpp.extn_attribute_char004  =       'FACTURA'
      group by  anc.party_id
          ,     hcp.email_address)
,   alp_limit_contact_facturacion as
    ( select  party_id
          ,   email
          ,   ROW_NUMBER()
              over( partition by    party_id
                    order by    creacion)       reg
      from    alp_contactos_facturacion)
,   alp_contact_facturacion as
    ( select  party_id
          ,   array_join(sort_array(collect_list(email)),', ') as emails
      from    alp_limit_contact_facturacion
      where   reg <= 5
      group by party_id)    
select  'AR'                        appl_source
    -- parties
,   CONCAT_WS('|', hp.partynumber, hca.accountnumber , hcsu.location)    client_ar_id
,   hp.partyid                  party_id
,   hp.partynumber              identificador_registro
,   hp.partyname                nombre
,   hp.partytype                tipo_cliente
,   hp.jgzzfiscalcode           numero_identificacion_contribuyente
,   op.attribute1               regimen_fiscal
,   flv1.meaning                desc_regimen_fiscal
,   op.attribute2               tipo_persona_fisica
,   flv2.meaning                desc_tipo_persona_fisica
--  parties_references
,   hosr1.origsystemrefid       party_osr_id
,   hosr1.origsystemreference   party_osr
--  accounts
,   hca.custaccountid           cust_account_id
,   hca.accountnumber           numero_cuenta_cliente
,   hca.accountname             nombre_cuenta_cliente
,   hca.customerclasscode       clase_cuenta
,   flv3.meaning                desc_clase_cuenta
,   hca.customertype            canal_tipo_cuenta
,   flv4.meaning                desc_canal_tipo_cuenta
,   flv4.tag                    canal_400_tipo_cuenta
,   hca.attribute1              grupo_comercial
,   flv5.meaning                desc_grupo_comercial
,   flv5.tag                    grupo_comercial_400
,   hca.attribute2              unidad_facturacion
,   flv6.meaning                desc_unidad_facturacion
,   hca.attribute3              forma_pago
,   flv7.meaning                desc_forma_pago
,   hca.attribute4              tipo_factura
,   flv8.meaning                desc_tipo_factura
,   hca.attribute5              metodo_pago
,   flv9.meaning                desc_metodo_pago
,   hca.attribute6              dias_gracia
,   hca.attribute7              codigo_bancario
,   hca.attribute8              subcanal
,   flv10.meaning               desc_subcanal
,   hca.attribute9              zona_cobranza
,   flv11.meaning               desc_zona_cobranza
,   hca.attribute10             dias_pago
,   hca.attribute11             dias_recepcion
,   hca.attribute12             grupo_concentrador
,   flv12.meaning               desc_grupo_concentrador
,   hca.attribute13             no_cliente_factura_concentrada
,   hca.attribute14             agrupacion_ibp
,   flv13.meaning               desc_agrupacion_ibp
,   flv13.tag                   agrupacion_ibp_400
,   hca.attribute15             timbrado_individual
,   hca.attribute16             uso_cfdi
,   flv14.meaning               desc_uso_cfdi
,   hca.attribute17             numero_proveedor
,   hca.attribute18             incoterm
,   hca.attribute19             tipo_addenda
,   flv26.meaning               desc_tipo_addenda
,   hca.attribute20             macro_canal
,   flv15.meaning               desc_macro_canal
,   hca.attribute21             giro_cliente
,   flv16.meaning               desc_giro_cliente
,   hca.attribute22             genera_addenda
,   hca.attribute23             referencia_cie
,   hca.attribute24             referencia_cuenta
--  account_references
,   hosr2.origsystemrefid       account_osr_id
,   hosr2.origsystemreference   account_osr
--  party_sites
,   hps.partysiteid             party_site_id
,   hps.partysitenumber         numero_sitio
,   hps.partysitename           nombre_sitio
,   hps.identifyingaddressflag  direccion_primaria
--  party_sites_refernces
,   hosr3.origsystemrefid       party_site_osr_id
,   hosr3.origsystemreference   party_site_osr
--  hz_locations
,   hl.locationid               location_id
,   hl.country                  pais
,   hl.postalcode               codigo_postal
,   hl.address1                 calle
,   hl.address2                 numero_exterior
,   hl.address3                 numero_interior
,   hl.city                     colonia
,   hl.county                   alcaldia_municipio
,   hl.state                    estado
,   hl.addrelementattribute1    entre_calle_1
,   hl.addrelementattribute2    entre_calle_2
,   hl.addrelementattribute3    latitud
,   hl.addrelementattribute4    longitud
,   hl.addrelementattribute5    codigo_gln   
--  locations_refrences
,   hosr7.origsystemrefid       location_osr_id
,   hosr7.origsystemreference   location_osr
--  account_sites
,   hcasa.custacctsiteid        cust_acct_site_id
,   hcasa.billtoflag            bill_to_flag
,   hcasa.shiptoflag            ship_to_flag
,   hcasa.attribute1            plaza_distribucion
,   flv17.meaning               desc_plaza_distribucion
,   flv17.tag                   plaza_distribucion_400
,   hcasa.attribute2            factura_anticipada
,   hcasa.attribute3            cedis_asignado
,   flv18.meaning               desc_cedis_asignado
,   flv18.tag                   cedis_asignado_400
,   hcasa.attribute4            no_sucursal_factura_concentrada
,   hcasa.attribute5            codigo_exportacion
,   hcasa.attribute6            zona_nielsen
,   flv19.meaning               desc_zona_nielsen
,   hcasa.attribute7            regional_responsable
,   flv20.meaning               desc_regional_responsable
,   hcasa.attribute8            tipo_entrega
,   flv21.meaning               desc_tipo_entrega
,   hcasa.attribute9            segmentacion_categoria_externa
,   flv22.meaning               desc_segmentacion_categoria_externa
,   hcasa.attribute10           categoria_interna
,   flv23.meaning               desc_categoria_interna
,   hcasa.attribute11           cuenta_bancaria
,   hcasa.attribute12           metodo_comercializacion
,   flv24.meaning               desc_metodo_comercializacion
,   hcasa.attribute13           codigo_sucursal
,   hcasa.attribute14           identificador_edi
,   hcasa.attribute15           zona_precios
,   flv25.meaning               desc_zona_precios
,   hcasa.attribute16           guid_chep
,   hcasa.attribute17           inspeccion_calidad
,   hcasa.attribute18           numero_tienda_retek
,   hcasa.attribute19           perfil_cliente
,   hcasa.attribute20           tipo_pedido_cliente
,   hcasa.attribute21           referencia_sucursal
--  account_site_references
,   hosr4.origsystemrefid       account_site_osr_id
,   hosr4.origsystemreference   account_site_osr
--  party_site_uses
,   hpsu.party_site_use_id
,   hpsu.primario_por_tipo
--  party_site_uses_references
,   hpsu.party_site_use_osr_id
,   hpsu.party_site_use_osr
--  account_site_uses
,   hcsu.siteuseid              site_use_id
,   hcsu.primaryflag            sitio_cuenta_primario
,   hcsu.siteusecode            uso_sitio_cuenta
,   hcsu.location               sitio
--  account_site_uses_references
,   hosr6.origsystemrefid       account_site_use_osr_id
,   hosr6.origsystemreference   account_site_use_osr
--  terminos de pago    
,   rtt.ratermtlname            terminos_pago
,   rtt.ratermtldescription     desc_terminos_pago
,   flv27.tag                   cedis_asignado_as400
,   acf.emails                  email_facturacion
from            cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partyextractpvo_stg                    hp
join        cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles_stg                      op
    on  hp.partyid  =   op.party_id
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountextractpvo_stg          hca
    on  hp.partyid  =   hca.partyid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountsiteextractpvo_stg      hcasa
    on  hca.custaccountid   =   hcasa.custaccountid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partysiteextractpvo_stg                hps
    on  hcasa.partysiteid   =   hps.partysiteid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_locationextractpvo_stg                 hl
    on  hps.locationid  =   hl.locationid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeracctsiteuseextractpvo_stg      hcsu
    on      hcasa.custacctsiteid    =   hcsu.custacctsiteid
join        alp_party_site_use                                                                        hpsu
    on      hps.partysiteid     =   hpsu.party_site_id
        and hcsu.siteusecode    =   hpsu.site_use_type
        and hpsu.reg            =   1
left join   cat_master_oracle_fusion.sch_gold_layer.fscm_fin_ar_paymenttermtlextractpvo_stg           rtt
    on      hcsu.paymenttermid      =   rtt.ratermtltermid
        and rtt.ratermtllanguage    =   'E'
left join   cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_setidsetspvo_stg                     fasis
    on      hcasa.setid     =   fasis.setid
        and fasis.language  =   'E'
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_stg  hosr1
    on  hp.partyid  =   hosr1.ownertableid
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_stg  hosr2
    on      hca.custaccountid       =   hosr2.ownertableid
        and hca.origsystemreference =   hosr2.origsystemreference
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_stg  hosr3
    on      hps.partysiteid             =       hosr3.ownertableid
        and hosr3.origsystemreference   like    'PS%'
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_stg  hosr4
    on      hcasa.custacctsiteid        =   hosr4.ownertableid
        and hosr4.origsystemreference   =   'FUSION'
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_stg  hosr6
    on  hcsu.siteuseid  =   hosr6.ownertableid
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_stg  hosr7
    on  hl.locationid   =   hosr7.ownertableid
left join   alp_cat_fnd_lookups                                                                       flv1
    on      op.attribute1     = flv1.code
        and flv1.type         = 'ALP_REGIMENFISCAL'
        and flv1.view_appl_id = 0
left join   alp_cat_fnd_lookups                                                                       flv2
    on      op.attribute2     = flv2.code
        and flv2.type         = 'ALP_TIPOFISCAL'    
        and flv2.view_appl_id = 0
left join   alp_cat_fnd_lookups                                                                       flv3
    on      hca.customerclasscode = flv3.code
        and flv3.type             = 'ALP_TIPOCREDITO'
        and flv3.view_appl_id     = 0
left join   alp_cat_fnd_lookups                                                                       flv4
    on      hca.customertype    = flv4.code
        and flv4.type           = 'ALP_CANAL'
        and flv4.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv5
    on      hca.attribute1      = flv5.code
        and flv5.type           = 'ALP_GRUPOCOMERCIAL'
        and flv5.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv6
    on      hca.attribute2      = flv6.code
        and flv6.type           = 'ALP_UNIDADFACTURACION'
        and flv6.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv7
    on      hca.attribute3      = flv7.code
        and flv7.type           = 'ALP_FORMAPAGO'
        and flv7.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv8
    on      hca.attribute4      = flv8.code
        and flv8.type           = 'ALP_TIPOFACTURA'
        and flv8.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv9
    on      hca.attribute5      = flv9.code
        and flv9.type           = 'ALP_METODOPAGO'
        and flv9.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv10
    on      hca.attribute8      = flv10.code
        and flv10.type          = 'ALP_SUBCANAL'  
        and flv10.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv11
    on      hca.attribute9      = flv11.code
        and flv11.type          = 'ALP_ZCOBRANZA'
        and flv11.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv12
    on      hca.attribute12     = flv12.code
        and flv12.type          = 'ALP_CONCENTRADOR'  
        and flv12.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv13
    on      hca.attribute14     = flv13.code
        and flv13.type          = 'ALP_IBP'   
        and flv13.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv14
    on      hca.attribute16     = flv14.code
        and flv14.type          = 'ALP_USOCFDI'   
        and flv14.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv15
    on      hca.attribute20     = flv15.code
        and flv15.type          = 'ALP_MACROCANAL'    
        and flv15.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv16
    on      hca.attribute21     = flv16.code
        and flv16.type          = 'ALP_GIRO'
        and flv16.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv17
    on      hcasa.attribute1    = flv17.code
        and flv17.type          = 'ALP_PLAZADISTRIBUCION'
        and flv17.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv18
    on      hcasa.attribute3    = flv18.code
        and flv18.type          = 'ALP_CEDIS'
        and flv18.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv19
    on      hcasa.attribute6    = flv19.code
        and flv19.type          = 'ALP_ZNIELSEN'  
        and flv19.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv20
    on      hcasa.attribute7    = flv20.code
        and flv20.type          = 'ALP_REGIONALRESP'  
        and flv20.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv21
    on      hcasa.attribute8    = flv21.code
        and flv21.type          = 'ALP_TIPOENTREGA'   
        and flv21.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv22
    on      hcasa.attribute9    = flv22.code
        and flv22.type          = 'ALP_SEGMENTACION'  
        and flv22.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv23
    on      hcasa.attribute10   = flv23.code
        and flv23.type          = 'ALP_CLASINTERNA'   
        and flv23.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv24
    on      hcasa.attribute12   = flv24.code
        and flv24.type          = 'ALP_METODOCOMER'
        and flv24.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv25
    on      hcasa.attribute15   = flv25.code
        and flv25.type          = 'ALP_ZONAPRECIOS'
        and flv25.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv26
    on      hca.attribute19     = flv26.code
        and flv26.type          = 'ALP_TIPOADENDA'
        and flv26.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv27
    on      hcasa.attribute3    = flv27.code
        and flv27.type          = 'ALP_CEDIS_CDM_AS400'
        and flv27.view_appl_id  = 0
left join   alp_contact_facturacion                                                                    acf
    on  hp.partyid  =   acf.party_id
where   1                       =       1
and     hp.partytype            =       'ORGANIZATION'
and     ((fasis.setname         =       'GPLP_SET'
and      hca.customerclasscode  =       'R')
or      (flv18.meaning          is      null
and      hca.customerclasscode  <>      'R')
or      flv18.meaning           like    concat('%',replace(fasis.setname,'_SET',''),'%'))

Databricks PROD

with  alp_party_site_use as
        (   select  row_number()
                    over (  partition by    hpsu.partysiteid
                                    ,       hpsu.siteusetype
                            order by    hosr5.creationdate)     reg
                ,   hpsu.partysiteid                            party_site_id
                ,   hpsu.siteusetype                            site_use_type
                ,   hpsu.partysiteuseid                         party_site_use_id
                ,   hpsu.primarypertype                         primario_por_tipo
                ,   hosr5.origsystemrefid                       party_site_use_osr_id
                ,   hosr5.origsystemreference                   party_site_use_osr
            from    cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partysiteuseextractpvo_prd               hpsu
                join  cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_prd  hosr5
                    on      hpsu.partysiteuseid         =       hosr5.ownertableid
                        and hosr5.origsystemreference   like    'PSU%')
, alp_cat_fnd_lookups as
    ( select  flv.lookuptype        type
          ,   flv.lookupcode        code
          ,   flvt.meaning    
          ,   flv.setid             set_id   
          ,   flv.tag
          ,   flv.viewapplicationid view_appl_id
          ,   flv.enabledflag       enabled
          ,   flv.startdateactive   start_date
          ,   flv.enddateactive     end_date
      from    cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluesextractpvo_prd   flv
        join  cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_prd flvt
          on    flv.lookuptype        = flvt.lookuptype
            and flv.lookupcode        = flvt.lookupcode
            and flv.viewapplicationid = flvt.viewapplicationid
                and   flvt.language         = 'E')
, alp_niveles_cdm as
    ( select  party_id
          ,   extn_attribute_char008  nivel
          ,   party_id                contact_party_id
      from    cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles_prd
      where   extn_attribute_char008  = 'ORGANIZACION'
      union all
      select  CAST(extn_attribute_char003 as double)    party_id  
          ,   extn_attribute_char008                    nivel
          ,   party_id                                  contact_party_id
      from    cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles_prd
      where   extn_attribute_char008  = 'CUENTA'
      union all
      select  CAST(c.extn_attribute_char003 as double)  party_id 
          ,   s.extn_attribute_char008                  nivel
          ,   s.party_id                                contact_party_id
      from    cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles_prd  c
        join  cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles_prd  s
          on  c.party_id                = s.extn_attribute_number001
      where   s.extn_attribute_char008  = 'SUCURSAL')
,   alp_contactos_facturacion as
    ( select  anc.party_id
          ,   hcp.email_address     email
          ,   MAX(hr.creation_date) creacion
      from    alp_niveles_cdm                                                 anc
        join  cat_master_oracle_fusion.sch_gold_layer.hz_relationships_prd    hr
          on  anc.contact_party_id          =       hr.subject_id
            and hr.subject_type             =       'ORGANIZATION'
            and hr.object_type              =       'PERSON'
            and hr.object_table_name        =       'HZ_PARTIES'
            and hr.relationship_code        =       'CONTACT'
            and current_date()              between hr.start_date
                                            and     hr.end_date 
        join  cat_master_oracle_fusion.sch_gold_layer.hz_contact_points_prd   hcp
          on  hr.object_id                  =       hcp.owner_table_id
            and hcp.contact_point_type      =       'EMAIL'
            and hcp.owner_table_name        =       'HZ_PARTIES'
            and hcp.orig_system_reference   is not  null
            and current_date()              between hcp.start_date
                                            and     hcp.end_date
        join  cat_master_oracle_fusion.sch_gold_layer.hz_person_profiles_prd  hpp
          on  hr.object_id                  =       hpp.party_id
            and hpp.extn_attribute_char004  =       'FACTURA'
      group by  anc.party_id
          ,     hcp.email_address)
,   alp_limit_contact_facturacion as
    ( select  party_id
          ,   email
          ,   ROW_NUMBER()
              over( partition by    party_id
                    order by    creacion)       reg
      from    alp_contactos_facturacion)
,   alp_contact_facturacion as
    ( select  party_id
          ,   array_join(sort_array(collect_list(email)),', ') as emails
      from    alp_limit_contact_facturacion
      where   reg <= 5
      group by party_id)    
select  'AR'                        appl_source
    -- parties
,   CONCAT_WS('|', hp.partynumber, hca.accountnumber , hcsu.location)    client_ar_id
,   hp.partyid                  party_id
,   hp.partynumber              identificador_registro
,   hp.partyname                nombre
,   hp.partytype                tipo_cliente
,   hp.jgzzfiscalcode           numero_identificacion_contribuyente
,   op.attribute1               regimen_fiscal
,   flv1.meaning                desc_regimen_fiscal
,   op.attribute2               tipo_persona_fisica
,   flv2.meaning                desc_tipo_persona_fisica
--  parties_references
,   hosr1.origsystemrefid       party_osr_id
,   hosr1.origsystemreference   party_osr
--  accounts
,   hca.custaccountid           cust_account_id
,   hca.accountnumber           numero_cuenta_cliente
,   hca.accountname             nombre_cuenta_cliente
,   hca.customerclasscode       clase_cuenta
,   flv3.meaning                desc_clase_cuenta
,   hca.customertype            canal_tipo_cuenta
,   flv4.meaning                desc_canal_tipo_cuenta
,   flv4.tag                    canal_400_tipo_cuenta
,   hca.attribute1              grupo_comercial
,   flv5.meaning                desc_grupo_comercial
,   flv5.tag                    grupo_comercial_400
,   hca.attribute2              unidad_facturacion
,   flv6.meaning                desc_unidad_facturacion
,   hca.attribute3              forma_pago
,   flv7.meaning                desc_forma_pago
,   hca.attribute4              tipo_factura
,   flv8.meaning                desc_tipo_factura
,   hca.attribute5              metodo_pago
,   flv9.meaning                desc_metodo_pago
,   hca.attribute6              dias_gracia
,   hca.attribute7              codigo_bancario
,   hca.attribute8              subcanal
,   flv10.meaning               desc_subcanal
,   hca.attribute9              zona_cobranza
,   flv11.meaning               desc_zona_cobranza
,   hca.attribute10             dias_pago
,   hca.attribute11             dias_recepcion
,   hca.attribute12             grupo_concentrador
,   flv12.meaning               desc_grupo_concentrador
,   hca.attribute13             no_cliente_factura_concentrada
,   hca.attribute14             agrupacion_ibp
,   flv13.meaning               desc_agrupacion_ibp
,   flv13.tag                   agrupacion_ibp_400
,   hca.attribute15             timbrado_individual
,   hca.attribute16             uso_cfdi
,   flv14.meaning               desc_uso_cfdi
,   hca.attribute17             numero_proveedor
,   hca.attribute18             incoterm
,   hca.attribute19             tipo_addenda
,   flv26.meaning               desc_tipo_addenda
,   hca.attribute20             macro_canal
,   flv15.meaning               desc_macro_canal
,   hca.attribute21             giro_cliente
,   flv16.meaning               desc_giro_cliente
,   hca.attribute22             genera_addenda
,   hca.attribute23             referencia_cie
,   hca.attribute24             referencia_cuenta
--  account_references
,   hosr2.origsystemrefid       account_osr_id
,   hosr2.origsystemreference   account_osr
--  party_sites
,   hps.partysiteid             party_site_id
,   hps.partysitenumber         numero_sitio
,   hps.partysitename           nombre_sitio
,   hps.identifyingaddressflag  direccion_primaria
--  party_sites_refernces
,   hosr3.origsystemrefid       party_site_osr_id
,   hosr3.origsystemreference   party_site_osr
--  hz_locations
,   hl.locationid               location_id
,   hl.country                  pais
,   hl.postalcode               codigo_postal
,   hl.address1                 calle
,   hl.address2                 numero_exterior
,   hl.address3                 numero_interior
,   hl.city                     colonia
,   hl.county                   alcaldia_municipio
,   hl.state                    estado
,   hl.addrelementattribute1    entre_calle_1
,   hl.addrelementattribute2    entre_calle_2
,   hl.addrelementattribute3    latitud
,   hl.addrelementattribute4    longitud
,   hl.addrelementattribute5    codigo_gln   
--  locations_refrences
,   hosr7.origsystemrefid       location_osr_id
,   hosr7.origsystemreference   location_osr
--  account_sites
,   hcasa.custacctsiteid        cust_acct_site_id
,   hcasa.billtoflag            bill_to_flag
,   hcasa.shiptoflag            ship_to_flag
,   hcasa.attribute1            plaza_distribucion
,   flv17.meaning               desc_plaza_distribucion
,   flv17.tag                   plaza_distribucion_400
,   hcasa.attribute2            factura_anticipada
,   hcasa.attribute3            cedis_asignado
,   flv18.meaning               desc_cedis_asignado
,   flv18.tag                   cedis_asignado_400
,   hcasa.attribute4            no_sucursal_factura_concentrada
,   hcasa.attribute5            codigo_exportacion
,   hcasa.attribute6            zona_nielsen
,   flv19.meaning               desc_zona_nielsen
,   hcasa.attribute7            regional_responsable
,   flv20.meaning               desc_regional_responsable
,   hcasa.attribute8            tipo_entrega
,   flv21.meaning               desc_tipo_entrega
,   hcasa.attribute9            segmentacion_categoria_externa
,   flv22.meaning               desc_segmentacion_categoria_externa
,   hcasa.attribute10           categoria_interna
,   flv23.meaning               desc_categoria_interna
,   hcasa.attribute11           cuenta_bancaria
,   hcasa.attribute12           metodo_comercializacion
,   flv24.meaning               desc_metodo_comercializacion
,   hcasa.attribute13           codigo_sucursal
,   hcasa.attribute14           identificador_edi
,   hcasa.attribute15           zona_precios
,   flv25.meaning               desc_zona_precios
,   hcasa.attribute16           guid_chep
,   hcasa.attribute17           inspeccion_calidad
,   hcasa.attribute18           numero_tienda_retek
,   hcasa.attribute19           perfil_cliente
,   hcasa.attribute20           tipo_pedido_cliente
,   hcasa.attribute21           referencia_sucursal
--  account_site_references
,   hosr4.origsystemrefid       account_site_osr_id
,   hosr4.origsystemreference   account_site_osr
--  party_site_uses
,   hpsu.party_site_use_id
,   hpsu.primario_por_tipo
--  party_site_uses_references
,   hpsu.party_site_use_osr_id
,   hpsu.party_site_use_osr
--  account_site_uses
,   hcsu.siteuseid              site_use_id
,   hcsu.primaryflag            sitio_cuenta_primario
,   hcsu.siteusecode            uso_sitio_cuenta
,   hcsu.location               sitio
--  account_site_uses_references
,   hosr6.origsystemrefid       account_site_use_osr_id
,   hosr6.origsystemreference   account_site_use_osr
--  terminos de pago    
,   rtt.ratermtlname            terminos_pago
,   rtt.ratermtldescription     desc_terminos_pago
,   flv27.tag                   cedis_asignado_as400
,   acf.emails                  email_facturacion
from            cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partyextractpvo_prd                    hp
join        cat_master_oracle_fusion.sch_gold_layer.hz_organization_profiles_prd                      op
    on  hp.partyid  =   op.party_id
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountextractpvo_prd          hca
    on  hp.partyid  =   hca.partyid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountsiteextractpvo_prd      hcasa
    on  hca.custaccountid   =   hcasa.custaccountid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partysiteextractpvo_prd                hps
    on  hcasa.partysiteid   =   hps.partysiteid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_locationextractpvo_prd                 hl
    on  hps.locationid  =   hl.locationid
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeracctsiteuseextractpvo_prd      hcsu
    on      hcasa.custacctsiteid    =   hcsu.custacctsiteid
join        alp_party_site_use                                                                        hpsu
    on      hps.partysiteid     =   hpsu.party_site_id
        and hcsu.siteusecode    =   hpsu.site_use_type
        and hpsu.reg            =   1
left join   cat_master_oracle_fusion.sch_gold_layer.fscm_fin_ar_paymenttermtlextractpvo_prd           rtt
    on      hcsu.paymenttermid      =   rtt.ratermtltermid
        and rtt.ratermtllanguage    =   'E'
left join   cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_setidsetspvo_prd                     fasis
    on      hcasa.setid     =   fasis.setid
        and fasis.language  =   'E'
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_prd  hosr1
    on  hp.partyid  =   hosr1.ownertableid
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_prd  hosr2
    on      hca.custaccountid       =   hosr2.ownertableid
        and hca.origsystemreference =   hosr2.origsystemreference
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_prd  hosr3
    on      hps.partysiteid             =       hosr3.ownertableid
        and hosr3.origsystemreference   like    'PS%'
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_prd  hosr4
    on      hcasa.custacctsiteid        =   hosr4.ownertableid
        and hosr4.origsystemreference   =   'FUSION'
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_prd  hosr6
    on  hcsu.siteuseid  =   hosr6.ownertableid
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_originalsystemreferenceextractpvo_prd  hosr7
    on  hl.locationid   =   hosr7.ownertableid
left join   alp_cat_fnd_lookups                                                                       flv1
    on      op.attribute1     = flv1.code
        and flv1.type         = 'ALP_REGIMENFISCAL'
        and flv1.view_appl_id = 0
left join   alp_cat_fnd_lookups                                                                       flv2
    on      op.attribute2     = flv2.code
        and flv2.type         = 'ALP_TIPOFISCAL'    
        and flv2.view_appl_id = 0
left join   alp_cat_fnd_lookups                                                                       flv3
    on      hca.customerclasscode = flv3.code
        and flv3.type             = 'ALP_TIPOCREDITO'
        and flv3.view_appl_id     = 0
left join   alp_cat_fnd_lookups                                                                       flv4
    on      hca.customertype    = flv4.code
        and flv4.type           = 'ALP_CANAL'
        and flv4.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv5
    on      hca.attribute1      = flv5.code
        and flv5.type           = 'ALP_GRUPOCOMERCIAL'
        and flv5.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv6
    on      hca.attribute2      = flv6.code
        and flv6.type           = 'ALP_UNIDADFACTURACION'
        and flv6.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv7
    on      hca.attribute3      = flv7.code
        and flv7.type           = 'ALP_FORMAPAGO'
        and flv7.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv8
    on      hca.attribute4      = flv8.code
        and flv8.type           = 'ALP_TIPOFACTURA'
        and flv8.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv9
    on      hca.attribute5      = flv9.code
        and flv9.type           = 'ALP_METODOPAGO'
        and flv9.view_appl_id   = 0
left join   alp_cat_fnd_lookups                                                                       flv10
    on      hca.attribute8      = flv10.code
        and flv10.type          = 'ALP_SUBCANAL'  
        and flv10.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv11
    on      hca.attribute9      = flv11.code
        and flv11.type          = 'ALP_ZCOBRANZA'
        and flv11.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv12
    on      hca.attribute12     = flv12.code
        and flv12.type          = 'ALP_CONCENTRADOR'  
        and flv12.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv13
    on      hca.attribute14     = flv13.code
        and flv13.type          = 'ALP_IBP'   
        and flv13.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv14
    on      hca.attribute16     = flv14.code
        and flv14.type          = 'ALP_USOCFDI'   
        and flv14.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv15
    on      hca.attribute20     = flv15.code
        and flv15.type          = 'ALP_MACROCANAL'    
        and flv15.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv16
    on      hca.attribute21     = flv16.code
        and flv16.type          = 'ALP_GIRO'
        and flv16.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv17
    on      hcasa.attribute1    = flv17.code
        and flv17.type          = 'ALP_PLAZADISTRIBUCION'
        and flv17.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv18
    on      hcasa.attribute3    = flv18.code
        and flv18.type          = 'ALP_CEDIS'
        and flv18.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv19
    on      hcasa.attribute6    = flv19.code
        and flv19.type          = 'ALP_ZNIELSEN'  
        and flv19.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv20
    on      hcasa.attribute7    = flv20.code
        and flv20.type          = 'ALP_REGIONALRESP'  
        and flv20.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv21
    on      hcasa.attribute8    = flv21.code
        and flv21.type          = 'ALP_TIPOENTREGA'   
        and flv21.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv22
    on      hcasa.attribute9    = flv22.code
        and flv22.type          = 'ALP_SEGMENTACION'  
        and flv22.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv23
    on      hcasa.attribute10   = flv23.code
        and flv23.type          = 'ALP_CLASINTERNA'   
        and flv23.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv24
    on      hcasa.attribute12   = flv24.code
        and flv24.type          = 'ALP_METODOCOMER'
        and flv24.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv25
    on      hcasa.attribute15   = flv25.code
        and flv25.type          = 'ALP_ZONAPRECIOS'
        and flv25.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv26
    on      hca.attribute19     = flv26.code
        and flv26.type          = 'ALP_TIPOADENDA'
        and flv26.view_appl_id  = 0
left join   alp_cat_fnd_lookups                                                                       flv27
    on      hcasa.attribute3    = flv27.code
        and flv27.type          = 'ALP_CEDIS_CDM_AS400'
        and flv27.view_appl_id  = 0
left join   alp_contact_facturacion                                                                    acf
    on  hp.partyid  =   acf.party_id
where   1                       =       1
and     hp.partytype            =       'ORGANIZATION'
and     ((fasis.setname         =       'GPLP_SET'
and      hca.customerclasscode  =       'R')
or      (flv18.meaning          is      null
and      hca.customerclasscode  <>      'R')
or      flv18.meaning           like    concat('%',replace(fasis.setname,'_SET',''),'%'))

Oracle

WITH  alp_party_site_use AS
    ( SELECT  ROW_NUMBER()
              OVER (  PARTITION BY  hpsu.party_site_id
                            ,       hpsu.site_use_type
                      ORDER BY hosr5.creation_date)     reg
          ,   hpsu.party_site_id                            
          ,   hpsu.site_use_type                            
          ,   hpsu.party_site_use_id                         
          ,   hpsu.primary_per_type                     primario_por_tipo
          ,   hosr5.orig_system_ref_id                  party_site_use_osr_id
          ,   hosr5.orig_system_reference               party_site_use_osr
      FROM    hz_party_site_uses      hpsu
          ,   hz_orig_sys_references  hosr5
      WHERE   hpsu.party_site_use_id      =     hosr5.owner_table_id
      AND     hosr5.orig_system_reference LIKE  'PSU%')
,   alp_cat_fnd_lookups as
    ( SELECT  lookup_type         type
          ,   lookup_code         code
          ,   meaning    
          ,   set_id   
          ,   tag
          ,   view_application_id view_appl_id
          ,   enabled_flag        enabled
          ,   start_date_active   start_date
          ,   end_date_active     end_date
      FROM    fnd_lookup_values
      WHERE   language  = 'E')
,   alp_niveles_cdm AS
    ( SELECT  party_id
          ,   extn_attribute_char008  nivel
          ,   party_id                contact_party_id
      FROM    hz_organization_profiles  o
      WHERE   extn_attribute_char008  = 'ORGANIZACION'
      UNION ALL
      SELECT  TO_NUMBER(extn_attribute_char003) party_id  
          ,   extn_attribute_char008            nivel
          ,   party_id                          contact_party_id
      FROM    hz_organization_profiles
      WHERE   extn_attribute_char008  = 'CUENTA'
      UNION ALL
      SELECT  TO_NUMBER(c.extn_attribute_char003) party_id 
          ,   s.extn_attribute_char008            nivel
          ,   s.party_id                          contact_party_id
      FROM    hz_organization_profiles  c
          ,   hz_organization_profiles  s
      WHERE   c.party_id                = s.extn_attribute_number001
      AND     s.extn_attribute_char008  = 'SUCURSAL')
,   alp_contactos_facturacion AS
    ( SELECT  anc.party_id
          ,   hcp.email_address     email
          ,   MAX(hr.creation_date) creacion
      FROM    alp_niveles_cdm     anc
          ,   hz_relationships    hr
          ,   hz_contact_points   hcp
          ,   hz_person_profiles  hpp
      WHERE   anc.contact_party_id        =       hr.subject_id
      AND     hr.object_id                =       hcp.owner_table_id
      AND     hr.object_id                =       hpp.party_id
      AND     hr.subject_type             =       'ORGANIZATION'
      AND     hr.object_type              =       'PERSON'
      AND     hr.object_table_name        =       'HZ_PARTIES'
      AND     hr.relationship_code        =       'CONTACT'
      AND     hcp.contact_point_type      =       'EMAIL'
      AND     hcp.owner_table_name        =       'HZ_PARTIES'
      AND     hpp.extn_attribute_char004  =       'FACTURA'
      AND     hcp.orig_system_reference   IS NOT  NULL
      AND     SYSDATE                     BETWEEN hr.start_date
                                          AND     hr.end_date 
      AND     SYSDATE                     BETWEEN hcp.start_date
                                          AND     hcp.end_date
      GROUP BY  anc.party_id
          ,     hcp.email_address)
,   alp_limit_contact_facturacion AS
    ( SELECT  party_id
          ,   email
          ,   ROW_NUMBER()
              OVER( PARTITION BY party_id
                    ORDER BY  creacion)   reg
      FROM    alp_contactos_facturacion)
,   alp_contact_facturacion AS
    ( SELECT  party_id
          ,   LISTAGG(email, ', ')  
              WITHIN GROUP (ORDER BY party_id)  emails 
      FROM    alp_limit_contact_facturacion
      WHERE   reg <=  5
      GROUP BY  party_id) 
SELECT  'AR'                          appl_source
    -- parties
,   hp.party_number
    ||  '|'
    ||  hca.account_number      
    ||  '|'
    ||  hcsua.location            client_ar_id
,   hp.party_id
,   hp.party_number               identificador_registro
,   hp.party_name                 nombre
,   hp.party_type                 tipo_cliente
,   hp.jgzz_fiscal_code           numero_identificacion_contribuyente
,   op.attribute1                 regimen_fiscal
,   flv1.meaning                  desc_regimen_fiscal
,   op.attribute2                 tipo_persona_fisica
,   flv2.meaning                  desc_tipo_persona_fisica
--  parties_references
,   hosr1.orig_system_ref_id      party_osr_id
,   hosr1.orig_system_reference   party_osr
--  accounts
,   hca.cust_account_id
,   hca.account_number            numero_cuenta_cliente
,   hca.account_name              nombre_cuenta_cliente
,   hca.customer_class_code       clase_cuenta
,   flv3.meaning                  desc_clase_cuenta
,   hca.customer_type             canal_tipo_cuenta
,   flv4.meaning                  desc_canal_tipo_cuenta
,   flv4.tag                      canal_400_tipo_cuenta
,   hca.attribute1                grupo_comercial
,   flv5.meaning                  desc_grupo_comercial
,   flv5.tag                      grupo_comercial_400
,   hca.attribute2                unidad_facturacion
,   flv6.meaning                  desc_unidad_facturacion
,   hca.attribute3                forma_pago
,   flv7.meaning                  desc_forma_pago
,   hca.attribute4                tipo_factura
,   flv8.meaning                  desc_tipo_factura
,   hca.attribute5                metodo_pago
,   flv9.meaning                  desc_metodo_pago
,   hca.attribute6                dias_gracia
,   hca.attribute7                codigo_bancario
,   hca.attribute8                subcanal
,   flv10.meaning                 desc_subcanal
,   hca.attribute9                zona_cobranza
,   flv11.meaning                 desc_zona_cobranza
,   hca.attribute10               dias_pago
,   hca.attribute11               dias_recepcion
,   hca.attribute12               grupo_concentrador
,   flv12.meaning                 desc_grupo_concentrador
,   hca.attribute13               no_cliente_factura_concentrada
,   hca.attribute14               agrupacion_ibp
,   flv13.meaning                 desc_agrupacion_ibp
,   flv13.tag                     agrupacion_ibp_400
,   hca.attribute15               timbrado_individual
,   hca.attribute16               uso_cfdi
,   flv14.meaning                 desc_uso_cfdi
,   hca.attribute17               numero_proveedor
,   hca.attribute18               incoterm
,   hca.attribute19               tipo_addenda
,   flv26.meaning                 desc_tipo_addenda
,   hca.attribute20               macro_canal
,   flv15.meaning                 desc_macro_canal
,   hca.attribute21               giro_cliente
,   flv16.meaning                 desc_giro_cliente
,   hca.attribute22               genera_addenda
,   hca.attribute23               referencia_cie
,   hca.attribute24               referencia_cuenta
--  account_references
,   hosr2.orig_system_ref_id      account_osr_id
,   hosr2.orig_system_reference   account_osr
--  party_sites
,   hps.party_site_id
,   hps.party_site_number         numero_sitio
,   hps.party_site_name           nombre_sitio
,   hps.identifying_address_flag  direccion_primaria
--  party_sites_refernces
,   hosr3.orig_system_ref_id      party_site_osr_id
,   hosr3.orig_system_reference   party_site_osr
--  hz_locations
,   hl.location_id                location_id
,   hl.country                    pais
,   hl.postal_code                codigo_postal
,   hl.address1                   calle
,   hl.address2                   numero_exterior
,   hl.address3                   numero_interior
,   hl.city                       colonia
,   hl.county                     alcaldia_municipio
,   hl.state                      estado
,   hl.addr_element_attribute1    entre_calle_1
,   hl.addr_element_attribute2    entre_calle_2
,   hl.addr_element_attribute3    latitud
,   hl.addr_element_attribute4    longitud
,   hl.addr_element_attribute5    codigo_gln   
--  locations_refrences
,   hosr6.orig_system_ref_id      location_osr_id
,   hosr6.orig_system_reference   location_osr
--  account_sites
,   hcasa.cust_acct_site_id       cust_acct_site_id
,   hcasa.bill_to_flag            
,   hcasa.ship_to_flag            
,   hcasa.attribute1              plaza_distribucion
,   flv17.meaning                 desc_plaza_distribucion
,   flv17.tag                     plaza_distribucion_400
,   hcasa.attribute2              factura_anticipada
,   hcasa.attribute3              cedis_asignado
,   flv18.meaning                 desc_cedis_asignado
,   flv18.tag                     cedis_asignado_400
,   hcasa.attribute4              no_sucursal_factura_concentrada
,   hcasa.attribute5              codigo_exportacion
,   hcasa.attribute6              zona_nielsen
,   flv19.meaning                 desc_zona_nielsen
,   hcasa.attribute7              regional_responsable
,   flv20.meaning                 desc_regional_responsable
,   hcasa.attribute8              tipo_entrega
,   flv21.meaning                 desc_tipo_entrega
,   hcasa.attribute9              segmentacion_categoria_externa
,   flv22.meaning                 desc_segmentacion_categoria_externa
,   hcasa.attribute10             categoria_interna
,   flv23.meaning                 desc_categoria_interna
,   hcasa.attribute11             cuenta_bancaria
,   hcasa.attribute12             metodo_comercializacion
,   flv24.meaning                 desc_metodo_comercializacion
,   hcasa.attribute13             codigo_sucursal
,   hcasa.attribute14             identificador_edi
,   hcasa.attribute15             zona_precios
,   flv25.meaning                 desc_zona_precios
,   hcasa.attribute16             guid_chep
,   hcasa.attribute17             inspeccion_calidad
,   hcasa.attribute18             numero_tienda_retek
,   hcasa.attribute19             perfil_cliente
,   hcasa.attribute20             tipo_pedido_cliente
,   hcasa.attribute21             referencia_sucursal
--  account_site_references
,   hosr4.orig_system_ref_id      account_site_osr_id
,   hosr4.orig_system_reference   account_site_osr
--  party_site_uses
,   hpsu.party_site_use_id
,   hpsu.primario_por_tipo
--  party_site_uses_references
,   hpsu.party_site_use_osr_id
,   hpsu.party_site_use_osr
--  account_site_uses
,   hcsua.site_use_id
,   hcsua.primary_flag                sitio_cuenta_primario
,   hcsua.site_use_code               uso_sitio_cuenta
,   hcsua.location                    sitio
--  account_site_uses_references
,   hosr5.orig_system_ref_id          account_site_use_osr_id
,   hosr5.orig_system_reference       account_site_use_osr
--  terminos de pago    
,   rtt.name                          terminos_pago
,   rtt.description                   desc_terminos_pago
,   flv27.tag                         cedis_asignado_as400
,   acf.emails                        email_facturacion
FROM  hz_parties                hp
,   hz_organization_profiles  op
,   hz_cust_accounts          hca
,   hz_cust_acct_sites_all    hcasa
,   hz_party_sites            hps
,   hz_locations              hl
,   hz_cust_site_uses_all     hcsua
,   alp_party_site_use        hpsu
,   ra_terms_tl               rtt
,   fnd_setid_sets            fasis
,   alp_contact_facturacion   acf
,   hz_orig_sys_references    hosr1 
,   hz_orig_sys_references    hosr2
,   hz_orig_sys_references    hosr3
,   hz_orig_sys_references    hosr4
,   hz_orig_sys_references    hosr5
,   hz_orig_sys_references    hosr6
,   alp_cat_fnd_lookups       flv1
,   alp_cat_fnd_lookups       flv2
,   alp_cat_fnd_lookups       flv3
,   alp_cat_fnd_lookups       flv4
,   alp_cat_fnd_lookups       flv5
,   alp_cat_fnd_lookups       flv6
,   alp_cat_fnd_lookups       flv7
,   alp_cat_fnd_lookups       flv8
,   alp_cat_fnd_lookups       flv9
,   alp_cat_fnd_lookups       flv10
,   alp_cat_fnd_lookups       flv11
,   alp_cat_fnd_lookups       flv12
,   alp_cat_fnd_lookups       flv13
,   alp_cat_fnd_lookups       flv14
,   alp_cat_fnd_lookups       flv15
,   alp_cat_fnd_lookups       flv16
,   alp_cat_fnd_lookups       flv17
,   alp_cat_fnd_lookups       flv18
,   alp_cat_fnd_lookups       flv19
,   alp_cat_fnd_lookups       flv20
,   alp_cat_fnd_lookups       flv21
,   alp_cat_fnd_lookups       flv22
,   alp_cat_fnd_lookups       flv23
,   alp_cat_fnd_lookups       flv24
,   alp_cat_fnd_lookups       flv25
,   alp_cat_fnd_lookups       flv26
,   alp_cat_fnd_lookups       flv27
WHERE hp.party_id                           =     op.party_id 
AND   hp.party_id                           =     hca.party_id
AND   hca.cust_account_id                   =     hcasa.cust_account_id
AND   hcasa.party_site_id                   =     hps.party_site_id
AND   hps.location_id                       =     hl.location_id
AND   hcasa.cust_acct_site_id               =     hcsua.cust_acct_site_id
AND   hps.party_site_id                     =     hpsu.party_site_id
AND   hcsua.site_use_code                   =     hpsu.site_use_type
AND   hcsua.payment_term_id                 =     rtt.term_id(+)
AND   hcasa.set_id                          =     fasis.set_id
AND   hp.party_id                           =     acf.party_id(+)
AND   hp.party_id                           =     hosr1.owner_table_id(+)
AND   hca.cust_account_id                   =     hosr2.owner_table_id(+)
AND   hca.orig_system_reference             =     hosr2.orig_system_reference(+)
AND   hps.party_site_id                     =     hosr3.owner_table_id(+)
AND   hcasa.cust_acct_site_id               =     hosr4.owner_table_id(+)
AND   hcsua.site_use_id                     =     hosr5.owner_table_id(+)
AND   hl.location_id                        =     hosr6.owner_table_id(+)
AND   op.attribute1                         =     flv1.code(+)
AND   op.attribute2                         =     flv2.code(+)
AND   hca.customer_class_code               =     flv3.code(+)
AND   hca.customer_type                     =     flv4.code(+)
AND   hca.attribute1                        =     flv5.code(+)
AND   hca.attribute2                        =     flv6.code(+)
AND   hca.attribute3                        =     flv7.code(+)
AND   hca.attribute4                        =     flv8.code(+)
AND   hca.attribute5                        =     flv9.code(+)
AND   hca.attribute8                        =     flv10.code(+)
AND   hca.attribute9                        =     flv11.code(+)
AND   hca.attribute12                       =     flv12.code(+)
AND   hca.attribute14                       =     flv13.code(+)
AND   hca.attribute16                       =     flv14.code(+)
AND   hca.attribute20                       =     flv15.code(+)
AND   hca.attribute21                       =     flv16.code(+)
AND   hcasa.attribute1                      =     flv17.code(+)
AND   hcasa.attribute3                      =     flv18.code(+)
AND   hcasa.attribute6                      =     flv19.code(+)
AND   hcasa.attribute7                      =     flv20.code(+)
AND   hcasa.attribute8                      =     flv21.code(+)
AND   hcasa.attribute9                      =     flv22.code(+)
AND   hcasa.attribute10                     =     flv23.code(+)
AND   hcasa.attribute12                     =     flv24.code(+)
AND   hcasa.attribute15                     =     flv25.code(+)
AND   hca.attribute19                       =     flv26.code(+)
AND   hcasa.attribute3                      =     flv27.code(+)
AND   flv1.type(+)                          =     'ALP_REGIMENFISCAL'
AND   flv2.type(+)                          =     'ALP_TIPOFISCAL'
AND   flv3.type(+)                          =     'ALP_TIPOCREDITO'
AND   flv4.type(+)                          =     'ALP_CANAL'
AND   flv5.type(+)                          =     'ALP_GRUPOCOMERCIAL'
AND   flv6.type(+)                          =     'ALP_UNIDADFACTURACION'
AND   flv7.type(+)                          =     'ALP_FORMAPAGO'
AND   flv8.type(+)                          =     'ALP_TIPOFACTURA'
AND   flv9.type(+)                          =     'ALP_METODOPAGO'
AND   flv10.type(+)                         =     'ALP_SUBCANAL'
AND   flv11.type(+)                         =     'ALP_ZCOBRANZA'
AND   flv12.type(+)                         =     'ALP_CONCENTRADOR'
AND   flv13.type(+)                         =     'ALP_IBP'
AND   flv14.type(+)                         =     'ALP_USOCFDI'
AND   flv15.type(+)                         =     'ALP_MACROCANAL'
AND   flv16.type(+)                         =     'ALP_GIRO'
AND   flv17.type(+)                         =     'ALP_PLAZADISTRIBUCION'
AND   flv18.type(+)                         =     'ALP_CEDIS'
AND   flv19.type(+)                         =     'ALP_ZNIELSEN'
AND   flv20.type(+)                         =     'ALP_REGIONALRESP'
AND   flv21.type(+)                         =     'ALP_TIPOENTREGA'
AND   flv22.type(+)                         =     'ALP_SEGMENTACION'
AND   flv23.type(+)                         =     'ALP_CLASINTERNA'
AND   flv24.type(+)                         =     'ALP_METODOCOMER'
AND   flv25.type(+)                         =     'ALP_ZONAPRECIOS'
AND   flv26.type(+)                         =     'ALP_TIPOADENDA'
AND   flv27.type(+)                         =     'ALP_CEDIS_CDM_AS400'
AND   hosr4.orig_system_reference(+)        =     'FUSION'
AND   hp.party_type                         =     'ORGANIZATION'
AND   fasis.language                        =     'E'
AND   NVL(hosr3.orig_system_reference,'PS') LIKE  'PS%'
AND   flv1.view_appl_id(+)                  =     0
AND   flv2.view_appl_id(+)                  =     0
AND   flv3.view_appl_id(+)                  =     0
AND   flv4.view_appl_id(+)                  =     0
AND   flv5.view_appl_id(+)                  =     0
AND   flv6.view_appl_id(+)                  =     0
AND   flv7.view_appl_id(+)                  =     0
AND   flv8.view_appl_id(+)                  =     0
AND   flv9.view_appl_id(+)                  =     0
AND   flv10.view_appl_id(+)                 =     0
AND   flv11.view_appl_id(+)                 =     0
AND   flv12.view_appl_id(+)                 =     0
AND   flv13.view_appl_id(+)                 =     0
AND   flv14.view_appl_id(+)                 =     0
AND   flv15.view_appl_id(+)                 =     0
AND   flv16.view_appl_id(+)                 =     0
AND   flv17.view_appl_id(+)                 =     0
AND   flv18.view_appl_id(+)                 =     0
AND   flv19.view_appl_id(+)                 =     0
AND   flv20.view_appl_id(+)                 =     0
AND   flv21.view_appl_id(+)                 =     0
AND   flv22.view_appl_id(+)                 =     0
AND   flv23.view_appl_id(+)                 =     0
AND   flv24.view_appl_id(+)                 =     0
AND   flv25.view_appl_id(+)                 =     0
AND   flv26.view_appl_id(+)                 =     0
AND   flv27.view_appl_id(+)                 =     0
AND   hpsu.reg                              =     1
AND   ((fasis.set_name                      =     'GPLP_SET'
AND   hca.customer_class_code               =     'R')
OR    (flv18.meaning                        IS    NULL
AND   hca.customer_class_code               <>    'R')
OR    flv18.meaning                         LIKE  REPLACE(fasis.set_name,'_SET') || '%')

---