el aviso de credito es la nota de credito que se crea y el NF es el nro de factura asociada
esto de cancelamento debe ser para el tipo -> 'BR-Nota Fiscal Importada'
el juego que se ejecuta que tiene varios puntos para poder migrar las om -> --
contiene los concurrente que se explican mas abajo
juego -> XX OM Proceso de Ventas -> XXOMSALES (desde respo xx om super user o xx om superv)
* XX AP-AR Interfaz de Proveedores y Clientes -> interfaz viene de myo y otro lado y carga tabla xx_arap_cust_supp los altas y modificaciones de clientes y proveedores
* XX OM Interface de Pedidos de Ventas -> carga la info de la xx_om_sales
* XX INT Proceso de Actualizacion de Errores -> carga los correspondientes errores
* XX AR Workflow Background Process -> pasa la info a la interfaz de ar
* XX OM Interface de Pedidos de Ventas -> carga la info de la xx_om_sales
* XX INT Proceso de Actualizacion de Errores -> carga los correspondientes errores
* XX AR Workflow Background Process -> pasa la info a la interfaz de ar
--- en el caso de querer hacer paso por paso se detalla abajo como se hace
--------------------------------------------------------------------------------
0- en el caso de tener que dar de alta cliente o proveedor debemos modificar la tabla xx_arap_cust_supp
buscando el registro estepecifico y ponerlo en NEW
1 -se debe obtener las oms de la tabla xx_om_sales, se pueden importar por la burkutul
para pasar las OMS entre ambientes se hace un select de la tabla XX_OM_SALES. se copia la sentencia de insert y
se debe cambiar el campo STATUS -> NEW
EJEMPLO DE INSERT XX_OM_SALES
Insert into XX_OM_SALES
(ORIG_SYS_DOCUMENT_REF, LEGAL_ENTITY, SOURCE_SYSTEM_NUMBER, BILL_TO, ORDER_NUMBER, ORDER_SOURCE, ORDER_TYPE, ORDERED_DATE, TRANSACTIONAL_CURR_CODE, PAYMENT_TERM, ATTRIBUTE5_H, SALESREP, CANCEL_FLAG_H, INVENTORY_ITEM, ORDERED_QUANTITY, UNIT_SELLING_PRICE, LINE_TYPE, CONTEXT, ATTRIBUTE2_L, ATTRIBUTE4_L, ATTRIBUTE5_L, ATTRIBUTE6_L, ATTRIBUTE7_L, ATTRIBUTE8_L, CANCEL_FLAG_L, REQUEST_ID, STATUS, ORG_ID, LEGAL_ENTITY_ID, SHIP_TO_ORG_ID, ORDER_TYPE_ID, PAYMENT_TERM_ID, INVOICE_TO_ORG_ID, SOLD_TO_ORG_ID, LINE_TYPE_ID, INVENTORY_ITEM_ID, SALESREP_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, RESPONSIBILITY_ID, ORDER_SOURCE_ID, PRICE_LIST_ID, ORDER_CATEGORY_CODE, LINE_CATEGORY_CODE, CONTEXT_H, ATTRIBUTE9_L, ATTRIBUTE11_L, ATTRIBUTE14_L, ATTRIBUTE1_H, ATTRIBUTE2_H, ATTRIBUTE3_H, ATTRIBUTE6_H, ATTRIBUTE7_H, ATTRIBUTE8_H, ATTRIBUTE11_H, ATTRIBUTE12_H, LINE_NUMBER, LEGAL_ENTITY_CURR_CODE, CONVERSION_TYPE_CODE, ORDER_COUNTRY_CODE, ORG_COUNTRY_CODE, SOURCE_SYSTEM_NUMBER_NEW, RESP_APPL_ID, ORGANIZATION_ID_MASTER, ORGANIZATION_CODE_MASTER, ORDER_TYPE2, LINE_TYPE2, ORIG_SYS_LINE_REF, ORDER_TYPE_COLLECTION_TYPE, REQUEST_ID_POST, INVENTORY_ITEM_UOM, INVENTORY_ITEM_CAT, SOURCE_SYSTEM_NUMBER_MAP, OE_ORDER_LINE_ID, OE_HEADER_ID, TRX_SEQUENCE, BILL_TO_NEW, GATEWAY_EXISTS, EXTRA_ATTRIBUTE47_L, INVOICEABLE_ITEM_FLAG)
Values
('FC-978619100800-27444-0', 'Decolar.com Ltda', 'PESSOA_FISICA', '331967',
978619100800, 'XX_FULFILLMENT', 'BR-Hotel-PP', TO_DATE('08/28/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'BRL',
'CONTADO', 'BR', 'Canal Web BR CF DOM',
'N', 'HTL.FARE.NONTAX.PP', 1,
1531.53, 'BR-Hotel-PP-CF-INT', '0002', '38837644',
'Alex Jesus', '32172889806', '1017064_Park Inn Santos', '28/12/2019',
'01/01/2020', 'N', 52625672, 'NEW',
141, 23274, 527384,
1800, 2000, 527383,
342920, 2071, 2, 100019193, -1,
TO_DATE('12/27/2019 11:48:21', 'MM/DD/YYYY HH24:MI:SS'), 13667, TO_DATE('01/03/2020 09:13:50', 'MM/DD/YYYY HH24:MI:SS'), 31785748,
51696, 1093, 6011,
'MIXED', 'ORDER',
'0002', '28/08/2019',
'PAM', '11764119302',
'Alex Visses de Freitas Jesus', '32172889806', 'no_reply@despegar.com', 'USD', '0.2420080785', 'DP', 'Av Wallace Simonsen|1450',
'|bairro|3548708|SP|01058|09771211', 1, 'BRL', 'Corporate', 'BR', 'BR', 'PESSOA_FISICA-BR', 660, 401, 'B03', 'BR-Hotel-PP', 'BR-Hotel-PP-CF-INT',
'1', 'PP', 52636888, 'UN', 'FARE', 'PESSOA_FISICA', 221045806, 66464332, 27444, 'PESSOA_FISICA-BR-331967-BR', 'Y', 'wellinton.pinheiro', 'Y');
2 - luego de tenerlas en la tabla xx_om_sales se debe ejecutar el concurrente desde -> DSP XX OM Super User
XX OM Interface de Pedidos de Ventas
3 - luego de que finalice este concurrente se debe ver si las OMs se importaron correctamente
EJEMPLO DE QUERY
select ooh.*
FROM apps.oe_order_lines_all ool,
apps.oe_order_headers_all ooh
WHERE 1=1
AND ool.header_id = ooh.header_id
and ooh.orig_sys_document_ref in ('FC-978619100800-27444-0', 'FC-978619100800-49358-0',
'NC-978619100800-17359-0','NC-978619100800-53048-0')
el salesrep se obtiene con este query
SELECT jrs.salesrep_id, jrre.resource_name
FROM jtf_rs_resource_extns_vl jrre
,jtf_rs_salesreps jrs
WHERE 1=1--xos.salesrep = jrre.resource_name
AND jrre.resource_id = jrs.resource_id
AND 101 = jrs.org_id
and RESOURCE_NAME= 'Canal Mobile Smart UY CO'
4- para poder pasar las OMs a AR se debe ejecutar el concurrente desde -> DSP BR AR Super User
XX AR Workflow Background Process -> parametro Y , N
Por lo general este concurrente esta programado
Y hace que las OMS queden en la tabla ra_interface_lines_all
EJEMPLO DE QUERY
select ooh.*
FROM apps.ra_interface_lines_all ril,
apps.oe_order_lines_all ool,
apps.oe_order_headers_all ooh
WHERE ril.org_id = 141--l_norg_id
AND ril.interface_line_attribute6 = ool.line_id
AND ool.header_id = ooh.header_id
and ooh.orig_sys_document_ref in ('FC-978619100800-27444-0', 'FC-978619100800-49358-0',
'NC-978619100800-17359-0','NC-978619100800-53048-0')
5 - Para i portar de la Interface a las tablas de AR se debe ejecutar juego desde -> DSP BR AR Super User
-- este juego es solo para brasil ----
juego -> XX AR BR - Programa Mestre de NFFs Automáticas
tiene dentro estos concurrentes
--> XX AR BR - Corrige Aviso de Crédito de Cancelamento na Interface -> XXCANCELNFCOR -> XX_AR_BR_CANCEL_NOTA_FISCAL_PK.CANCEL_NF
--> Programa Principal de NFF Automática -> Programa Principal de Factura Automática
--> Programa Principal de NFF Automática - Interno -> Programa Principal de Factura Automática
--> XX AR BR - Processa Alteração de NF -> XXALTNFERRP -> XX_AR_BR_ALTER_INVOICE_PK.PROCESSA_CONC
--> XX AR BR - Envia à MS Cancelamento de NF -> XXCANCELNFMS -> XX_AR_BR_CANCEL_NOTA_FISCAL_PK.ATUALIZA_PREVIOUS_CUSTOMER_TRX
--- para el resto se usa -> XX AR Proceso de Facturación (pais)
juego -> XX AR Proceso de Facturación UY -> ejemplo asi desde la respo DSP UY AR SUPERV COBR
para validar que tipo de origen es para ejecutar el concurrete de importacion de transacciones- > XX AR Proceso de Facturación UY
se tiene que ver el line_type del query anterior, que es en la xx_om_sales
SELECT T1.TRANSACTION_TYPE_ID
,T1.NAME
,T1.DESCRIPTION
--,T2.*
,FLV_CAT.MEANING CATEGORIA_PEDIDO
,T2.TRANSACTION_TYPE_CODE
--,FLV_PHA.MEANING
--,DECODE(T2.ORDER_CATEGORY_CODE, 'ORDER', 'PEDIDO',T2.ORDER_CATEGORY_CODE) CATEGORIA_PEDIDO
,T2.START_DATE_ACTIVE FECHA_EFECTIVA_INICIO
--,T2.WAREHOUSE_ID
,WH.NAME DEPOSITO
--,T2.CUST_TRX_TYPE_ID
,T2.ATTRIBUTE3 DIAS_ESPERA
,T2.ATTRIBUTE4 EVENTO_GROSSING
,T2.ATTRIBUTE7 COBRADO_AEROLINEA
,LOKUP.MEANING TIPO_PRODUCTO
,T2.ATTRIBUTE9 TIPO_COBRO
,RBS1.NAME "ORIGEN FACTURA"
--,T2.INVOICE_SOURCE_ID --
,RBS2.NAME "ORIGEN FACTURA SIN ENTREGA"
,RCTT.NAME RELACION_TRX
--,T2.NON_DELIVERY_INVOICE_SOURCE_ID
--,T2.ATTRIBUTE7
--,T2.*
--AUDITORIA
,FU1.USER_NAME CREATED_BY
,T1.CREATION_DATE
,FU2.USER_NAME LAST_UPDATED_BY
,T1.LAST_UPDATE_DATE
FROM APPS.OE_TRANSACTION_TYPES_TL T1
,APPS.OE_TRANSACTION_TYPES_ALL T2
,APPS.RA_BATCH_SOURCES_ALL RBS1
,APPS.RA_BATCH_SOURCES_ALL RBS2
,APPS.RA_CUST_TRX_TYPES_ALL RCTT
,(SELECT LOOKUP_CODE, MEANING FROM APPS.FND_LOOKUP_VALUES WHERE LOOKUP_TYPE IN ('ORDER_CATEGORY') AND LANGUAGE = 'ESA') FLV_CAT
--,APPS.FND_LOOKUP_VALUES FLV_PHA
,APPS.HR_ALL_ORGANIZATION_UNITS HOU
,APPS.FND_USER FU1
,APPS.FND_USER FU2
--,APPS.OE_TRANSACTION_TYPES_TL OTTT_DEF
,(SELECT LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION, ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE
FROM APPS.FND_LOOKUP_VALUES LV
WHERE 1=1
AND LANGUAGE = 'ESA'
AND LOOKUP_TYPE = 'XX_OE_TIPO_PRODUCTO') LOKUP
,APPS.HR_ALL_ORGANIZATION_UNITS WH
WHERE 1=1
AND T2.ORG_ID = HOU.ORGANIZATION_ID (+)
AND T1.CREATED_BY = FU1.USER_ID -- CREADOR
AND T1.LAST_UPDATED_BY = FU2.USER_ID -- LAST_UPDATED_BY
AND T1.TRANSACTION_TYPE_ID = T2.TRANSACTION_TYPE_ID
AND T1.LANGUAGE = 'ESA'
AND T2.ATTRIBUTE8 = LOKUP.LOOKUP_CODE (+)
AND T2.ORDER_CATEGORY_CODE = FLV_CAT.LOOKUP_CODE (+)
AND T2.INVOICE_SOURCE_ID = RBS1.BATCH_SOURCE_ID (+)
AND T2.NON_DELIVERY_INVOICE_SOURCE_ID = RBS2.BATCH_SOURCE_ID (+)
AND T2.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID (+)
--AND T1.NAME LIKE '%T02%'
AND T2.WAREHOUSE_ID = WH.ORGANIZATION_ID (+)
AND HOU.NAME LIKE '% UY OU'
AND T1.NAME in('UY-Traspqt-PP-INT-DEV','UY-Hotelpqt-PP-INT-DEV') ----> este es el campo a evaluar
;
-- valido la creacion de la om y la fc
select rctl.interface_line_attribute6,rctl.sales_order
, ool.cust_po_number
, ooh.orig_sys_document_ref
, ool.header_id
, ooh.attribute16
from apps.ra_customer_Trx_all ril
, apps.ra_customer_trx_lines_all rctl
, apps.oe_order_lines_all ool
, apps.oe_order_headers_all ooh
where (rctl.interface_line_attribute6) = to_char(ool.line_id)
and ool.header_id = ooh.header_id
and ril.customer_trx_id = rctl.customer_Trx_id
and ooh.orig_sys_document_ref in ('FC-941377262400-0-FEE-0176-0000',
'NC-941377262400-0-INT-7911-0000',
'FC-941377262400-0-FEE-9545-0000',
'NC-941377262400-0-INT-2405-0000')
consulta general de la reserva mostrando todas las tablas que implican desde que llega la om hasta generar la fc
-------------------------------------------------------------------------------------------------------------------------
;
select xos.ATTRIBUTE1_H, xos.ATTRIBUTE2_H , XOS.SOURCE_SYSTEM_NUMBER, cust.GLOBAL_ATTRIBUTE10 COD_T_DOC, xos.orig_sys_document_ref,xos.status
,xos.legal_entity, xos.order_number, xos.ordered_date, xos.order_source, xos.orig_sys_document_ref,
xos.extra_attribute8_h, xos.transactional_curr_code, xos.attribute16_h, xos.attribute13_l, xos.line_type, xos.inventory_item,
fc.cuenta_linea, fc.cuenta_a_cobrar,
xos.unit_selling_price, xos.extra_attribute48_l, xos.extra_attribute49_l,xos.extra_attribute50_l,
xos.extra_attribute15_L, prov.vendor_name,
xos.attribute4_l, xos.status, xos.status_detail, xos.error_messages, xos.creation_date,
om.order_number o_vta, om.creation_date, om.flow_h, om.flow_l, int_ar.interface_ar_status,int_ar.interface_line_id,
fc.trx_number, fc.trx_date, fc.name, fc.amount_due_original, fc.amount_due_remaining, fc.doc_sequence_value, fc.attribute9 timbrada,
xos.orig_sys_reference, cust.party_name, extra_attribute13_h, FC.interface_line_attribute6, xos.attribute10_l, XOS.SALESREP,
xos.attribute2_L, xos.attribute12_h, xos.attribute11_h, xos.attribute7_l, xos.attribute8_l, om.TIPO_PEDIDO , FC.FACTURA_TIPO ,FC.TYPE_NAME, FC.type
from
-- Reservas --------------------------
(select legal_entity, order_number, ordered_date, orig_sys_document_ref, extra_attribute8_h, attribute16_h, inventory_item,
unit_selling_price, attribute4_l, status, error_messages, creation_date,
source_system_number || '-' || attribute5_h || '-' || bill_to orig_sys_reference, transactional_curr_code,
attribute13_l, extra_attribute13_h, attribute10_l, orig_sys_line_ref, extra_attribute48_l, extra_attribute49_l,
extra_attribute50_l, order_source, line_type, SALESREP, attribute2_L, attribute12_h, attribute11_h, attribute7_l, attribute8_l,
status_detail, extra_attribute15_L, attribute5_h ,ATTRIBUTE1_H, ATTRIBUTE2_H , SOURCE_SYSTEM_NUMBER
from bolinf.xx_om_sales) xos,
-- OM --------------------------------
(select ooh.order_number, ooh.orig_sys_document_ref, ooh.flow_status_code flow_h, ool.flow_status_code flow_l,
ooh.cust_po_number, ool.line_id, msib.segment1, ooh.creation_date, ool.orig_sys_line_ref, ool.line_number,OT.NAME TIPO_PEDIDO
from apps.oe_order_headers_all ooh,
apps.oe_order_lines_all ool,
APPS.OE_TRANSACTION_TYPES_TL OT,
apps.mtl_system_items_b msib
where ooh.header_id = ool.header_id
and ool.ordered_item_id = msib.inventory_item_id
and ool.ship_from_org_id = msib.organization_id
AND OOH.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID(+)
AND OT.LANGUAGE(+) = 'ESA'
) om,
-- Interface AR -------------------------------
(select ril.sales_order_line, ril.purchase_order, ril.sales_order, ril.interface_line_id,
decode(ril.interface_line_id,
null, decode(ril.purchase_order, null, 'OK', 'PENDIENTE'),
'ERROR') interface_ar_status
from apps.ra_interface_lines_all ril) int_ar,
-- FC -------------------------------
(select rct.trx_number, rct.trx_date, rbs.name, aps.amount_due_original, aps.amount_due_remaining, rct.CUST_TRX_TYPE_ID,
rctl.interface_line_attribute1, rctl.interface_line_attribute6, rct.purchase_order,
kfv1.concatenated_segments cuenta_linea, kfv2.concatenated_segments cuenta_a_cobrar,
rct.invoice_currency_code, rct.doc_sequence_value, rct.attribute9,ORI.NAME FACTURA_TIPO ,TYP.NAME TYPE_NAME, TYP.type type
from apps.ra_customer_trx_all rct,
apps.ra_customer_trx_lines_all rctl,
apps.ra_batch_sources_all rbs,
apps.ar_payment_schedules_all aps,
apps.ra_cust_trx_line_gl_dist_all gld,
apps.gl_code_combinations_kfv kfv1,
apps.ra_cust_trx_line_gl_dist_all gld2,
apps.gl_code_combinations_kfv kfv2
,APPS.RA_BATCH_SOURCES_ALL ORI
,APPS.RA_CUST_TRX_TYPES_ALL TYP
where rct.customer_trx_id = rctl.customer_trx_id
and rctl.line_type = 'LINE'
AND RCT.BATCH_SOURCE_ID = ORI.BATCH_SOURCE_ID(+)
AND ORI.ORG_ID(+) = RCT.ORG_ID
AND TYP.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
and rct.batch_source_id = rbs.batch_source_id
and rct.customer_trx_id = aps.customer_trx_id
and rctl.customer_trx_line_id = gld.customer_trx_line_id
and gld.code_combination_id = kfv1.code_combination_id
and rct.customer_trx_id = gld2.customer_trx_id
and gld2.account_class = 'REC'
and gld2.code_combination_id = kfv2.code_combination_id) fc,
-- Customer -----------------------
(select hp.party_name, hcas.orig_system_reference orig_system_reference1, hcas.org_id org_id1 , hca.GLOBAL_ATTRIBUTE10
from apps.hz_cust_acct_sites_all hcas,
apps.hz_cust_accounts_all hca,
apps.hz_parties hp
where hcas.cust_account_id = hca.cust_account_id
and hca.party_id = hp.party_id) cust,
-- Proveedor / Prestador 3ros ----------------------------------------------
(select prov.vendor_id, prov.vendor_name, prov.num_1099, prov.global_attribute12,
prov.num_1099||prov.global_attribute12 documento, prov.vat_registration_num,
(select s_prov.org_id
from apps.ap_supplier_sites_all s_prov
where s_prov.vendor_id = prov.vendor_id
and rownum = 1) org_id
from apps.ap_suppliers prov ) prov
----------------------------------------------------------------------------
where 1=1
AND om.line_id = fc.interface_line_attribute6(+)
and om.cust_po_number = fc.purchase_order(+)
and om.cust_po_number = int_ar.purchase_order(+)
and om.order_number = int_ar.sales_order(+)
and om.line_number = int_ar.sales_order_line(+)
and xos.inventory_item = om.segment1(+)
and xos.orig_sys_line_ref = om.orig_sys_line_ref(+)
and xos.orig_sys_document_ref = om.orig_sys_document_ref(+)
and xos.orig_sys_reference = cust.orig_system_reference1(+)
and nvl(xos.extra_attribute50_L,'@') = prov.documento(+)
and (xos.extra_attribute15_L IS NULL OR xos.extra_attribute15_L ||'-'|| xos.attribute5_h = prov.vat_registration_num)
and cust.org_id1 = prov.org_id(+)
-- Parametros de busqueda ----------------------------------------------
--and xos.legal_entity = 'Despegar.com.ar SA'
--and xos.orig_sys_document_ref IN (
--and xos.error_messages is null
--and fc.customer_trx_id = 589210064
--AND xos.ordered_date > SYSDATE - 720 --39JEU4
--AND xos.attribute2_L= '39JEU4'
--and XOS.SALESREP like '%-BT0%'
------------------------------------------------------------------------
--and xos.order_number IN ('1706002647','716123245900')--666629207100 --251050238200 901922240000
--and xos.order_number IN ('63842498001','6191112498001') --
--114 5436
and xos.order_number = '546223280150' --IN ('934244259900','440554259900')
;
--------------------------------------------------------------------------------
0- en el caso de tener que dar de alta cliente o proveedor debemos modificar la tabla xx_arap_cust_supp
buscando el registro estepecifico y ponerlo en NEW
1 -se debe obtener las oms de la tabla xx_om_sales, se pueden importar por la burkutul
para pasar las OMS entre ambientes se hace un select de la tabla XX_OM_SALES. se copia la sentencia de insert y
se debe cambiar el campo STATUS -> NEW
EJEMPLO DE INSERT XX_OM_SALES
Insert into XX_OM_SALES
(ORIG_SYS_DOCUMENT_REF, LEGAL_ENTITY, SOURCE_SYSTEM_NUMBER, BILL_TO, ORDER_NUMBER, ORDER_SOURCE, ORDER_TYPE, ORDERED_DATE, TRANSACTIONAL_CURR_CODE, PAYMENT_TERM, ATTRIBUTE5_H, SALESREP, CANCEL_FLAG_H, INVENTORY_ITEM, ORDERED_QUANTITY, UNIT_SELLING_PRICE, LINE_TYPE, CONTEXT, ATTRIBUTE2_L, ATTRIBUTE4_L, ATTRIBUTE5_L, ATTRIBUTE6_L, ATTRIBUTE7_L, ATTRIBUTE8_L, CANCEL_FLAG_L, REQUEST_ID, STATUS, ORG_ID, LEGAL_ENTITY_ID, SHIP_TO_ORG_ID, ORDER_TYPE_ID, PAYMENT_TERM_ID, INVOICE_TO_ORG_ID, SOLD_TO_ORG_ID, LINE_TYPE_ID, INVENTORY_ITEM_ID, SALESREP_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, RESPONSIBILITY_ID, ORDER_SOURCE_ID, PRICE_LIST_ID, ORDER_CATEGORY_CODE, LINE_CATEGORY_CODE, CONTEXT_H, ATTRIBUTE9_L, ATTRIBUTE11_L, ATTRIBUTE14_L, ATTRIBUTE1_H, ATTRIBUTE2_H, ATTRIBUTE3_H, ATTRIBUTE6_H, ATTRIBUTE7_H, ATTRIBUTE8_H, ATTRIBUTE11_H, ATTRIBUTE12_H, LINE_NUMBER, LEGAL_ENTITY_CURR_CODE, CONVERSION_TYPE_CODE, ORDER_COUNTRY_CODE, ORG_COUNTRY_CODE, SOURCE_SYSTEM_NUMBER_NEW, RESP_APPL_ID, ORGANIZATION_ID_MASTER, ORGANIZATION_CODE_MASTER, ORDER_TYPE2, LINE_TYPE2, ORIG_SYS_LINE_REF, ORDER_TYPE_COLLECTION_TYPE, REQUEST_ID_POST, INVENTORY_ITEM_UOM, INVENTORY_ITEM_CAT, SOURCE_SYSTEM_NUMBER_MAP, OE_ORDER_LINE_ID, OE_HEADER_ID, TRX_SEQUENCE, BILL_TO_NEW, GATEWAY_EXISTS, EXTRA_ATTRIBUTE47_L, INVOICEABLE_ITEM_FLAG)
Values
('FC-978619100800-27444-0', 'Decolar.com Ltda', 'PESSOA_FISICA', '331967',
978619100800, 'XX_FULFILLMENT', 'BR-Hotel-PP', TO_DATE('08/28/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'BRL',
'CONTADO', 'BR', 'Canal Web BR CF DOM',
'N', 'HTL.FARE.NONTAX.PP', 1,
1531.53, 'BR-Hotel-PP-CF-INT', '0002', '38837644',
'Alex Jesus', '32172889806', '1017064_Park Inn Santos', '28/12/2019',
'01/01/2020', 'N', 52625672, 'NEW',
141, 23274, 527384,
1800, 2000, 527383,
342920, 2071, 2, 100019193, -1,
TO_DATE('12/27/2019 11:48:21', 'MM/DD/YYYY HH24:MI:SS'), 13667, TO_DATE('01/03/2020 09:13:50', 'MM/DD/YYYY HH24:MI:SS'), 31785748,
51696, 1093, 6011,
'MIXED', 'ORDER',
'0002', '28/08/2019',
'PAM', '11764119302',
'Alex Visses de Freitas Jesus', '32172889806', 'no_reply@despegar.com', 'USD', '0.2420080785', 'DP', 'Av Wallace Simonsen|1450',
'|bairro|3548708|SP|01058|09771211', 1, 'BRL', 'Corporate', 'BR', 'BR', 'PESSOA_FISICA-BR', 660, 401, 'B03', 'BR-Hotel-PP', 'BR-Hotel-PP-CF-INT',
'1', 'PP', 52636888, 'UN', 'FARE', 'PESSOA_FISICA', 221045806, 66464332, 27444, 'PESSOA_FISICA-BR-331967-BR', 'Y', 'wellinton.pinheiro', 'Y');
2 - luego de tenerlas en la tabla xx_om_sales se debe ejecutar el concurrente desde -> DSP XX OM Super User
XX OM Interface de Pedidos de Ventas
3 - luego de que finalice este concurrente se debe ver si las OMs se importaron correctamente
EJEMPLO DE QUERY
select ooh.*
FROM apps.oe_order_lines_all ool,
apps.oe_order_headers_all ooh
WHERE 1=1
AND ool.header_id = ooh.header_id
and ooh.orig_sys_document_ref in ('FC-978619100800-27444-0', 'FC-978619100800-49358-0',
'NC-978619100800-17359-0','NC-978619100800-53048-0')
el salesrep se obtiene con este query
SELECT jrs.salesrep_id, jrre.resource_name
FROM jtf_rs_resource_extns_vl jrre
,jtf_rs_salesreps jrs
WHERE 1=1--xos.salesrep = jrre.resource_name
AND jrre.resource_id = jrs.resource_id
AND 101 = jrs.org_id
and RESOURCE_NAME= 'Canal Mobile Smart UY CO'
4- para poder pasar las OMs a AR se debe ejecutar el concurrente desde -> DSP BR AR Super User
XX AR Workflow Background Process -> parametro Y , N
Por lo general este concurrente esta programado
Y hace que las OMS queden en la tabla ra_interface_lines_all
EJEMPLO DE QUERY
select ooh.*
FROM apps.ra_interface_lines_all ril,
apps.oe_order_lines_all ool,
apps.oe_order_headers_all ooh
WHERE ril.org_id = 141--l_norg_id
AND ril.interface_line_attribute6 = ool.line_id
AND ool.header_id = ooh.header_id
and ooh.orig_sys_document_ref in ('FC-978619100800-27444-0', 'FC-978619100800-49358-0',
'NC-978619100800-17359-0','NC-978619100800-53048-0')
5 - Para i portar de la Interface a las tablas de AR se debe ejecutar juego desde -> DSP BR AR Super User
-- este juego es solo para brasil ----
juego -> XX AR BR - Programa Mestre de NFFs Automáticas
tiene dentro estos concurrentes
--> XX AR BR - Corrige Aviso de Crédito de Cancelamento na Interface -> XXCANCELNFCOR -> XX_AR_BR_CANCEL_NOTA_FISCAL_PK.CANCEL_NF
--> Programa Principal de NFF Automática -> Programa Principal de Factura Automática
--> Programa Principal de NFF Automática - Interno -> Programa Principal de Factura Automática
--> XX AR BR - Processa Alteração de NF -> XXALTNFERRP -> XX_AR_BR_ALTER_INVOICE_PK.PROCESSA_CONC
--> XX AR BR - Envia à MS Cancelamento de NF -> XXCANCELNFMS -> XX_AR_BR_CANCEL_NOTA_FISCAL_PK.ATUALIZA_PREVIOUS_CUSTOMER_TRX
--- para el resto se usa -> XX AR Proceso de Facturación (pais)
juego -> XX AR Proceso de Facturación UY -> ejemplo asi desde la respo DSP UY AR SUPERV COBR
para validar que tipo de origen es para ejecutar el concurrete de importacion de transacciones- > XX AR Proceso de Facturación UY
se tiene que ver el line_type del query anterior, que es en la xx_om_sales
SELECT T1.TRANSACTION_TYPE_ID
,T1.NAME
,T1.DESCRIPTION
--,T2.*
,FLV_CAT.MEANING CATEGORIA_PEDIDO
,T2.TRANSACTION_TYPE_CODE
--,FLV_PHA.MEANING
--,DECODE(T2.ORDER_CATEGORY_CODE, 'ORDER', 'PEDIDO',T2.ORDER_CATEGORY_CODE) CATEGORIA_PEDIDO
,T2.START_DATE_ACTIVE FECHA_EFECTIVA_INICIO
--,T2.WAREHOUSE_ID
,WH.NAME DEPOSITO
--,T2.CUST_TRX_TYPE_ID
,T2.ATTRIBUTE3 DIAS_ESPERA
,T2.ATTRIBUTE4 EVENTO_GROSSING
,T2.ATTRIBUTE7 COBRADO_AEROLINEA
,LOKUP.MEANING TIPO_PRODUCTO
,T2.ATTRIBUTE9 TIPO_COBRO
,RBS1.NAME "ORIGEN FACTURA"
--,T2.INVOICE_SOURCE_ID --
,RBS2.NAME "ORIGEN FACTURA SIN ENTREGA"
,RCTT.NAME RELACION_TRX
--,T2.NON_DELIVERY_INVOICE_SOURCE_ID
--,T2.ATTRIBUTE7
--,T2.*
--AUDITORIA
,FU1.USER_NAME CREATED_BY
,T1.CREATION_DATE
,FU2.USER_NAME LAST_UPDATED_BY
,T1.LAST_UPDATE_DATE
FROM APPS.OE_TRANSACTION_TYPES_TL T1
,APPS.OE_TRANSACTION_TYPES_ALL T2
,APPS.RA_BATCH_SOURCES_ALL RBS1
,APPS.RA_BATCH_SOURCES_ALL RBS2
,APPS.RA_CUST_TRX_TYPES_ALL RCTT
,(SELECT LOOKUP_CODE, MEANING FROM APPS.FND_LOOKUP_VALUES WHERE LOOKUP_TYPE IN ('ORDER_CATEGORY') AND LANGUAGE = 'ESA') FLV_CAT
--,APPS.FND_LOOKUP_VALUES FLV_PHA
,APPS.HR_ALL_ORGANIZATION_UNITS HOU
,APPS.FND_USER FU1
,APPS.FND_USER FU2
--,APPS.OE_TRANSACTION_TYPES_TL OTTT_DEF
,(SELECT LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION, ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE
FROM APPS.FND_LOOKUP_VALUES LV
WHERE 1=1
AND LANGUAGE = 'ESA'
AND LOOKUP_TYPE = 'XX_OE_TIPO_PRODUCTO') LOKUP
,APPS.HR_ALL_ORGANIZATION_UNITS WH
WHERE 1=1
AND T2.ORG_ID = HOU.ORGANIZATION_ID (+)
AND T1.CREATED_BY = FU1.USER_ID -- CREADOR
AND T1.LAST_UPDATED_BY = FU2.USER_ID -- LAST_UPDATED_BY
AND T1.TRANSACTION_TYPE_ID = T2.TRANSACTION_TYPE_ID
AND T1.LANGUAGE = 'ESA'
AND T2.ATTRIBUTE8 = LOKUP.LOOKUP_CODE (+)
AND T2.ORDER_CATEGORY_CODE = FLV_CAT.LOOKUP_CODE (+)
AND T2.INVOICE_SOURCE_ID = RBS1.BATCH_SOURCE_ID (+)
AND T2.NON_DELIVERY_INVOICE_SOURCE_ID = RBS2.BATCH_SOURCE_ID (+)
AND T2.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID (+)
--AND T1.NAME LIKE '%T02%'
AND T2.WAREHOUSE_ID = WH.ORGANIZATION_ID (+)
AND HOU.NAME LIKE '% UY OU'
AND T1.NAME in('UY-Traspqt-PP-INT-DEV','UY-Hotelpqt-PP-INT-DEV') ----> este es el campo a evaluar
;
-- valido la creacion de la om y la fc
select rctl.interface_line_attribute6,rctl.sales_order
, ool.cust_po_number
, ooh.orig_sys_document_ref
, ool.header_id
, ooh.attribute16
from apps.ra_customer_Trx_all ril
, apps.ra_customer_trx_lines_all rctl
, apps.oe_order_lines_all ool
, apps.oe_order_headers_all ooh
where (rctl.interface_line_attribute6) = to_char(ool.line_id)
and ool.header_id = ooh.header_id
and ril.customer_trx_id = rctl.customer_Trx_id
and ooh.orig_sys_document_ref in ('FC-941377262400-0-FEE-0176-0000',
'NC-941377262400-0-INT-7911-0000',
'FC-941377262400-0-FEE-9545-0000',
'NC-941377262400-0-INT-2405-0000')
consulta general de la reserva mostrando todas las tablas que implican desde que llega la om hasta generar la fc
-------------------------------------------------------------------------------------------------------------------------
;
select xos.ATTRIBUTE1_H, xos.ATTRIBUTE2_H , XOS.SOURCE_SYSTEM_NUMBER, cust.GLOBAL_ATTRIBUTE10 COD_T_DOC, xos.orig_sys_document_ref,xos.status
,xos.legal_entity, xos.order_number, xos.ordered_date, xos.order_source, xos.orig_sys_document_ref,
xos.extra_attribute8_h, xos.transactional_curr_code, xos.attribute16_h, xos.attribute13_l, xos.line_type, xos.inventory_item,
fc.cuenta_linea, fc.cuenta_a_cobrar,
xos.unit_selling_price, xos.extra_attribute48_l, xos.extra_attribute49_l,xos.extra_attribute50_l,
xos.extra_attribute15_L, prov.vendor_name,
xos.attribute4_l, xos.status, xos.status_detail, xos.error_messages, xos.creation_date,
om.order_number o_vta, om.creation_date, om.flow_h, om.flow_l, int_ar.interface_ar_status,int_ar.interface_line_id,
fc.trx_number, fc.trx_date, fc.name, fc.amount_due_original, fc.amount_due_remaining, fc.doc_sequence_value, fc.attribute9 timbrada,
xos.orig_sys_reference, cust.party_name, extra_attribute13_h, FC.interface_line_attribute6, xos.attribute10_l, XOS.SALESREP,
xos.attribute2_L, xos.attribute12_h, xos.attribute11_h, xos.attribute7_l, xos.attribute8_l, om.TIPO_PEDIDO , FC.FACTURA_TIPO ,FC.TYPE_NAME, FC.type
from
-- Reservas --------------------------
(select legal_entity, order_number, ordered_date, orig_sys_document_ref, extra_attribute8_h, attribute16_h, inventory_item,
unit_selling_price, attribute4_l, status, error_messages, creation_date,
source_system_number || '-' || attribute5_h || '-' || bill_to orig_sys_reference, transactional_curr_code,
attribute13_l, extra_attribute13_h, attribute10_l, orig_sys_line_ref, extra_attribute48_l, extra_attribute49_l,
extra_attribute50_l, order_source, line_type, SALESREP, attribute2_L, attribute12_h, attribute11_h, attribute7_l, attribute8_l,
status_detail, extra_attribute15_L, attribute5_h ,ATTRIBUTE1_H, ATTRIBUTE2_H , SOURCE_SYSTEM_NUMBER
from bolinf.xx_om_sales) xos,
-- OM --------------------------------
(select ooh.order_number, ooh.orig_sys_document_ref, ooh.flow_status_code flow_h, ool.flow_status_code flow_l,
ooh.cust_po_number, ool.line_id, msib.segment1, ooh.creation_date, ool.orig_sys_line_ref, ool.line_number,OT.NAME TIPO_PEDIDO
from apps.oe_order_headers_all ooh,
apps.oe_order_lines_all ool,
APPS.OE_TRANSACTION_TYPES_TL OT,
apps.mtl_system_items_b msib
where ooh.header_id = ool.header_id
and ool.ordered_item_id = msib.inventory_item_id
and ool.ship_from_org_id = msib.organization_id
AND OOH.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID(+)
AND OT.LANGUAGE(+) = 'ESA'
) om,
-- Interface AR -------------------------------
(select ril.sales_order_line, ril.purchase_order, ril.sales_order, ril.interface_line_id,
decode(ril.interface_line_id,
null, decode(ril.purchase_order, null, 'OK', 'PENDIENTE'),
'ERROR') interface_ar_status
from apps.ra_interface_lines_all ril) int_ar,
-- FC -------------------------------
(select rct.trx_number, rct.trx_date, rbs.name, aps.amount_due_original, aps.amount_due_remaining, rct.CUST_TRX_TYPE_ID,
rctl.interface_line_attribute1, rctl.interface_line_attribute6, rct.purchase_order,
kfv1.concatenated_segments cuenta_linea, kfv2.concatenated_segments cuenta_a_cobrar,
rct.invoice_currency_code, rct.doc_sequence_value, rct.attribute9,ORI.NAME FACTURA_TIPO ,TYP.NAME TYPE_NAME, TYP.type type
from apps.ra_customer_trx_all rct,
apps.ra_customer_trx_lines_all rctl,
apps.ra_batch_sources_all rbs,
apps.ar_payment_schedules_all aps,
apps.ra_cust_trx_line_gl_dist_all gld,
apps.gl_code_combinations_kfv kfv1,
apps.ra_cust_trx_line_gl_dist_all gld2,
apps.gl_code_combinations_kfv kfv2
,APPS.RA_BATCH_SOURCES_ALL ORI
,APPS.RA_CUST_TRX_TYPES_ALL TYP
where rct.customer_trx_id = rctl.customer_trx_id
and rctl.line_type = 'LINE'
AND RCT.BATCH_SOURCE_ID = ORI.BATCH_SOURCE_ID(+)
AND ORI.ORG_ID(+) = RCT.ORG_ID
AND TYP.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
and rct.batch_source_id = rbs.batch_source_id
and rct.customer_trx_id = aps.customer_trx_id
and rctl.customer_trx_line_id = gld.customer_trx_line_id
and gld.code_combination_id = kfv1.code_combination_id
and rct.customer_trx_id = gld2.customer_trx_id
and gld2.account_class = 'REC'
and gld2.code_combination_id = kfv2.code_combination_id) fc,
-- Customer -----------------------
(select hp.party_name, hcas.orig_system_reference orig_system_reference1, hcas.org_id org_id1 , hca.GLOBAL_ATTRIBUTE10
from apps.hz_cust_acct_sites_all hcas,
apps.hz_cust_accounts_all hca,
apps.hz_parties hp
where hcas.cust_account_id = hca.cust_account_id
and hca.party_id = hp.party_id) cust,
-- Proveedor / Prestador 3ros ----------------------------------------------
(select prov.vendor_id, prov.vendor_name, prov.num_1099, prov.global_attribute12,
prov.num_1099||prov.global_attribute12 documento, prov.vat_registration_num,
(select s_prov.org_id
from apps.ap_supplier_sites_all s_prov
where s_prov.vendor_id = prov.vendor_id
and rownum = 1) org_id
from apps.ap_suppliers prov ) prov
----------------------------------------------------------------------------
where 1=1
AND om.line_id = fc.interface_line_attribute6(+)
and om.cust_po_number = fc.purchase_order(+)
and om.cust_po_number = int_ar.purchase_order(+)
and om.order_number = int_ar.sales_order(+)
and om.line_number = int_ar.sales_order_line(+)
and xos.inventory_item = om.segment1(+)
and xos.orig_sys_line_ref = om.orig_sys_line_ref(+)
and xos.orig_sys_document_ref = om.orig_sys_document_ref(+)
and xos.orig_sys_reference = cust.orig_system_reference1(+)
and nvl(xos.extra_attribute50_L,'@') = prov.documento(+)
and (xos.extra_attribute15_L IS NULL OR xos.extra_attribute15_L ||'-'|| xos.attribute5_h = prov.vat_registration_num)
and cust.org_id1 = prov.org_id(+)
-- Parametros de busqueda ----------------------------------------------
--and xos.legal_entity = 'Despegar.com.ar SA'
--and xos.orig_sys_document_ref IN (
--and xos.error_messages is null
--and fc.customer_trx_id = 589210064
--AND xos.ordered_date > SYSDATE - 720 --39JEU4
--AND xos.attribute2_L= '39JEU4'
--and XOS.SALESREP like '%-BT0%'
------------------------------------------------------------------------
--and xos.order_number IN ('1706002647','716123245900')--666629207100 --251050238200 901922240000
--and xos.order_number IN ('63842498001','6191112498001') --
--114 5436
and xos.order_number = '546223280150' --IN ('934244259900','440554259900')
;
Comentarios
Publicar un comentario