ES PARA CREAR DESDE LA XX_PO_PROV EN LA PO_HEADERS_ALL Y PO_LINES_ALL
CARGAR LOS DATOS DE PROVIOSION
1- AGARRO EL CASO QUE NECESITO DE LA TABLA XX_PO_PROV Y GUARDO EL INSERT
from xx_po_prov
where document_num = '6617886161250' -- document_num es el nro de reserva
;
2 - DEBO CAMBIAR ESTOS CAMPOS para el reprocesamiento
status = 'NEW'
ORIG_SYS_DOCUMENT_REF ->6617886161250-30e7c9b418095a10300711652-1 => 6617886161250-30e7c9b418095a10300711652-2
TRX_SEQUENCE 1201 => 1202
DSP AR PO SUPER USER -> XX PO Interface de Provisiones -> XXPOIPRO -> EN EL PRIMER PARAMETRO PONGO EL OSDR
-- luego de correr este la linea queda en status INTRFACE
-- SE EJECUTA AUTOMATICAMENTE EL -> Import Standard Purchase Orders
--PARA %BR%
DSP AR PO SUPER USER -> XX INT Proceso de Actualizacion de Errores -> XXINTPAE -> poner en el parametro SI A PO ,EL RESTO DE LOS PARAMETROS QUE NO
-- luego de correr este la linea queda en status PROCESSED
debo aprobar la po con la api y luego correr el concurrente
DSP UY OPER GL SUPER USER -> XX GL Interface de Provision de PO ->
--- desde la xx_po_prov el document_num lo puedo buscar en la po_headers_all por el campo comments o por el attribute13
from po_headers_all
where comments = '6617886161250'; -- DOCUMENT_NUM
from apps.po_headers_all
where note_to_vendor = '6617886161250-30e7c9b418095a10300711652-1' -- ORIG_SYS_DOCUMENT_REF
where attribute2 = 'mocked7891672314726'
from po_headers_all PH, po_LINEs_all PL
where PH.attribute13 = '6617886161250' -- DOCUMENT_NUM
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
order by ph.CREATION_DATE desc
FROM XX_GL_PRPO
WHERE 1=1
--AND reference1 = '8231956-6617886161250-10300711652' --- REFERENCE + CRO_HIJO
AND REFERENCE21 = '16991720' -- PO_HEADER_ID
FROM GL_INTERFACE
WHERE REFERENCE26 = '16991720'--PO_HEADER_ID
---------------------------------------------------------
este query muestra que que la po esta en interface o ya fue procesado y esta en la tabla de PO_headers y po_lines
xpp.orig_sys_document_ref --, xpp.document_num--, xpp.vendor_id, xpp.vendor_site_id,xpp.org_id, xpp.attribute2_l,
-- xpp.attribute9_l, xpp.inventory_item_id, xpp.attribute5_l, xpp.attribute2_h,
-- xpp.attribute15_h, xpp.attribute10_l
from xx_po_prov xpp, apps.po_lines_all pl ,apps.po_headers_all ph
where xpp.status IN ('INTERFACE','NEW')
and xpp.creation_Date between to_date('24/07/2022 06:54:16','dd/mm/yyyy hh24:mi:ss') and to_date('26/07/2022 11:26:52','dd/mm/yyyy hh24:mi:ss')
and xpp.document_num = ph.reference_num
AND xpp.vendor_id = ph.vendor_id
AND xpp.org_id = ph.org_id
AND ph.po_header_id = pl.po_header_id
AND xpp.attribute2_l = pl.attribute2
AND NVL(xpp.attribute9_l, '@X')= NVL(pl.attribute9, '@X')
AND xpp.inventory_item_id = pl.item_id
AND nvl(xpp.attribute5_l, '@X')= nvl(pl.attribute5, '@X')
AND xpp.orig_sys_document_ref = ph.note_to_vendor
AND xpp.vendor_site_id = ph.vendor_site_id
AND nvl(xpp.attribute2_h, '@X')= nvl(ph.attribute2, '@X')
AND NVL(xpp.attribute15_h,'X') = NVL(ph.attribute15,'X')
AND NVL(xpp.attribute10_l,'X') = NVL(pl.attribute10, 'X')
and pl.po_line_id in (select pl.po_line_id
from xx_po_prov xpp, apps.po_lines_all pl ,apps.po_headers_all ph
where xpp.status IN ('INTERFACE','NEW')
and xpp.creation_Date between to_date('24/07/2022 06:54:16','dd/mm/yyyy hh24:mi:ss') and to_date('26/07/2022 11:26:52','dd/mm/yyyy hh24:mi:ss')
and xpp.document_num = ph.reference_num
AND xpp.vendor_id = ph.vendor_id
AND xpp.org_id = ph.org_id
AND ph.po_header_id = pl.po_header_id
AND xpp.attribute2_l = pl.attribute2
AND NVL(xpp.attribute9_l, '@X')= NVL(pl.attribute9, '@X')
AND xpp.inventory_item_id = pl.item_id
AND nvl(xpp.attribute5_l, '@X')= nvl(pl.attribute5, '@X')
AND xpp.orig_sys_document_ref = ph.note_to_vendor
AND xpp.vendor_site_id = ph.vendor_site_id
AND nvl(xpp.attribute2_h, '@X')= nvl(ph.attribute2, '@X')
AND NVL(xpp.attribute15_h,'X') = NVL(ph.attribute15,'X')
AND NVL(xpp.attribute10_l,'X') = NVL(pl.attribute10, 'X')
having count(*) > 1
group by pl.po_line_id)
--order by 1
--------------------------------
gjh.name "Nombre Asiento",
gjl.reference_6,
gjh.currency_code "Divisa",
gjh.doc_sequence_value "Nro Documento",
gjh.reversed_je_header_id,
gjh.je_header_id,
gjs.user_je_source_name "Origen",
gjc.USER_JE_CATEGORY_NAME "Categoria",
gjh.period_name "Periodo",
glcc.segment4,
gjh.currency_conversion_rate,
gjh.currency_conversion_type,
gjh.CURRENCY_CONVERSION_DATE,
gjh.ACCRUAL_REV_PERIOD_NAME,
gjb.name "Nombre Lote",
gjl.DESCRIPTION,
DECODE(gjh.status, 'P','Contabilizado', 'U', 'No Contabilizado', gjh.status) "Estado",
gjb.creation_date "Fecha Creación",
gjb.running_total_dr "Débitos Totales",
gjb.running_total_cr "Créditos Totales",
gjl.effective_date "Fecha Efectiva",
gjh.DEFAULT_EFFECTIVE_DATE,
gjl.je_line_num "Nro Linea",
glcc.concatenated_segments "Combinacion Contable",
gjl.entered_dr "Importe Debito MO",
gjl.entered_cr "Importe Credito MO",
gjl.accounted_dr "Importe Debito MF",
gjl.accounted_cr "Importe Credito MF",
gjh.PARENT_JE_HEADER_ID,
gjh.ledger_id,
gjh.je_batch_id,
gjh.actual_flag,
gjh.accrual_rev_flag,
gjh.posted_date,
gjh.creation_date,
gjb.last_updated_by,
gjh.last_updated_by,
gjb.last_update_date,
gjh.last_update_date,
gjb.REQUEST_ID,
gjh.*
--select count (distinct gjb.je_batch_id)
from apps.gl_je_batches gjb,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_je_sources_vl gjs,
apps.GL_JE_CATEGORIES gjc,
apps.gl_ledgers l,
apps.gl_code_combinations_kfv glcc
where gjb.je_batch_id = gjh.je_batch_id
and gjh.je_header_id = gjl.je_header_id
AND gjh.ledger_id = l.ledger_id
AND gjl.code_combination_id = glcc.code_combination_id
AND gjh.je_source = gjs.je_source_name
and gjh.ledger_id = l.ledger_id
AND gjh.je_category = gjc.je_category_name
-- AND gjh.period_name IN ('MAY-21')--,'SEP-19')-- ('SEP-19','OCT-19','NOV-19','DIC-19','ENE-20','FEB-20')
-- AND gjl.period_name IN ('MAY-21')--,'SEP-19')--,'ENE-20') --('SEP-19','OCT-19','NOV-19','DIC-19','ENE-20','FEB-20')
and gjl.reference_6 = '16984719'--'17723762'--, 16490618 -- po_header_id
order by gjh.ledger_id, gjh.doc_sequence_value, gjl.je_line_num, gjb.creation_date ;
FROM GL_INTERFACE
--WHERE REFERENCE26 = '16984719' -- po_header_id
WHERE LEDGER_ID = 2070
AND GROUP_ID = 2070
--AND
SELECT * FROM XX_GL_PRPO
WHERE REFERENCE21 = '16984719' -- po_header_id
------------------------------
--cursor c_po_trx
SELECT po_trx.code_combination_id,
po_trx.attribute5,---po_trx.phad.xx_po_prov_status,
'N' om_exists,
'PROCESSING' STATUS,
po_trx.xx_po_prov_status,
po_trx.po_header_id,
po_trx.po_line_id,
po_trx.extra_attributes_flag,
po_trx.postventa_id,
po_trx.pnr_number/* 12.1 Intercompany */
,
po_trx.implemented,
po_trx.om_legal_entity_id,
po_trx.po_number,
po_trx.po_legal_entity_id,
po_trx.org_id,
po_trx.attribute_category,
po_trx.reference,
po_trx.cro_hijo,
po_trx.segment1 segment1,
po_trx.segment2,
po_trx.segment3,
po_trx.segment4,
po_trx.segment5,
po_trx.segment6,
po_trx.segment7,
po_trx.segment8,
po_trx.segment9,
po_trx.segment10,
po_trx.segment11,
po_trx.segment1 segment1_ic_po,
po_trx.segment2_ic_po segment2_ic_po,
po_trx.segment3_ic_po segment3_ic_po,
po_trx.segment4 segment4_ic_po,
po_trx.segment1 segment1_ic_om,
po_trx.segment2_ic_om_ic segment2_ic_om,
po_trx.segment3_ic_om_ic segment3_ic_om,
po_trx.segment4 segment4_ic_om,
po_trx.segment1 segment1_ic_po_pte_om,
po_trx.segment2_ic_po segment2_ic_po_pte_om,
po_trx.segment3_ic_po segment3_ic_po_pte_om,
po_trx.segment4 segment4_ic_po_pte_om,
po_trx.segment1 segment1_ic_po_pte_ac,
po_trx.segment2_ic_po segment2_ic_po_pte_ac,
po_trx.segment3_ic_po segment3_ic_po_pte_ac,
po_trx.segment4 segment4_ic_po_pte_ac,
po_trx.segment1 segment1_ic_po_pte_fe,
po_trx.segment2_ic_po segment2_ic_po_pte_fe,
po_trx.segment3_ic_po segment3_ic_po_pte_fe,
po_trx.segment4 segment4_ic_po_pte_fe,
po_trx.segment1 segment1_ic_po_pte_dt,
po_trx.segment2_ic_po segment2_ic_po_pte_dt,
po_trx.segment3_ic_po segment3_ic_po_pte_dt,
po_trx.segment4 segment4_ic_po_pte_dt,
po_trx.segment1 segment1_ic_om_pte_om,
'21211' segment2_ic_om_pte_om, --G_IC_PO_CTA_PTE_OM
po_trx.segment3_ic_om segment3_ic_om_pte_om,
po_trx.segment4 segment4_ic_om_pte_om,
po_trx.segment1 segment1_ic_om_pte_ac,
'41103' segment2_ic_om_pte_ac, ----G_IC_PO_CTA_PTE_AC
po_trx.segment3_ic_om segment3_ic_om_pte_ac,
po_trx.segment4 segment4_ic_om_pte_ac,
po_trx.segment1 segment1_ic_om_pte_fe,
'44011' segment2_ic_om_pte_fe, ----G_IC_PO_CTA_PTE_FE
po_trx.segment3_ic_om segment3_ic_om_pte_fe,
po_trx.segment4 segment4_ic_om_pte_fe,
po_trx.segment1 segment1_ic_om_pte_dt,
'53111' segment2_ic_om_pte_dt, --G_IC_PO_CTA_PTE_DT
po_trx.segment3_ic_om segment3_ic_om_pte_dt,
po_trx.segment4 segment4_ic_om_pte_dt--witbor added
,
po_trx.segment1 segment1_ic_om_pte_trn,
'11339' segment2_ic_om_pte_trn, --G_IC_PO_CTA_TRN_D
po_trx.segment3_ic_om segment3_ic_om_pte_trn,
po_trx.segment4 segment4_ic_om_pte_trn--end witbor added
,
po_trx.segment1 segment1_ic_gl3,
po_trx.segment2_ic_om segment2_ic_gl3,
po_trx.segment3_ic_om segment3_ic_gl3,
po_trx.segment4 segment4_ic_gl3,
po_trx.ledger_id ledger_id_om,
po_trx.currency_code,
po_trx.rate_type,
po_trx.rate_date,
po_trx.rate_type_om,
po_trx.rate_date_om,
po_trx.ledger_id,
TO_CHAR (NULL) user_je_source_name,
TO_CHAR (NULL) user_je_category_name,
po_trx.currency_code_le_om,
NVL (po_trx.new_model_vendor_flag, 'N') new_model_vendor_flag,
DECODE (po_trx.gl_event, 'DEBIT', po_trx.total, 0) entered_dr,
DECODE (po_trx.gl_event, 'CREDIT', po_trx.total, 0)
entered_cr,
DECODE (po_trx.gl_event, 'DEBIT', po_trx.total, 0)
entered_dr_vat,
DECODE (po_trx.gl_event, 'CREDIT', po_trx.total, 0)
entered_cr_vat,
DECODE (po_trx.gl_event, 'DEBIT', po_trx.amount_le_om, 0)
entered_amount_le_om_dr,
DECODE (po_trx.gl_event, 'CREDIT', po_trx.amount_le_om, 0)
entered_amount_le_om_cr,
DECODE (po_trx.gl_event, 'DEBIT', po_trx.amount_le_om, 0)
entered_amount_le_om_dr_vat,
DECODE (po_trx.gl_event, 'CREDIT', po_trx.amount_le_om, 0)
entered_amount_le_om_cr_vat,
DECODE (po_trx.gl_event,
'DEBIT', ROUND (po_trx.ajuste_costo_le_om, 2),
0)
entered_aj_costo_le_om_dr,
DECODE (po_trx.gl_event,
'CREDIT', ROUND (po_trx.ajuste_costo_le_om, 2),
0)
entered_aj_costo_le_om_cr,
DECODE (po_trx.gl_event,
'DEBIT', ROUND (po_trx.ajuste_costo_le_om, 2),
0)
entered_aj_costo_le_om_dr_vat,
DECODE (po_trx.gl_event,
'CREDIT', ROUND (po_trx.ajuste_costo_le_om, 2),
0)
entered_aj_costo_le_om_cr_vat,
DECODE (po_trx.gl_event,
'DEBIT', ROUND (po_trx.fee_le_om, 2),
0)
entered_fee_le_om_dr,
DECODE (po_trx.gl_event,
'CREDIT', ROUND (po_trx.fee_le_om, 2),
0)
entered_fee_le_om_cr,
DECODE (po_trx.gl_event,
'DEBIT', ROUND (po_trx.fee_le_om, 2),
0)
entered_fee_le_om_dr_vat,
DECODE (po_trx.gl_event,
'CREDIT', ROUND (po_trx.fee_le_om, 2),
0)
entered_fee_le_om_cr_vat,
DECODE (po_trx.gl_event,
'DEBIT', ROUND (po_trx.descuento_le_om, 2),
0)
entered_descuento_le_om_dr,
DECODE (po_trx.gl_event,
'CREDIT', ROUND (po_trx.descuento_le_om, 2),
0)
entered_descuento_le_om_cr,
DECODE (po_trx.gl_event,
'DEBIT', ROUND (po_trx.descuento_le_om, 2),
0)
entered_descuento_le_om_dr_vat,
DECODE (po_trx.gl_event,
'CREDIT', ROUND (po_trx.descuento_le_om, 2),
0)
entered_descuento_le_om_cr_vat--witbor added
,
DECODE (po_trx.gl_event,
'DEBIT', ROUND (po_trx.trn_le_om, 2),
0)
entered_trn_le_om_dr,
DECODE (po_trx.gl_event,
'CREDIT', ROUND (po_trx.trn_le_om, 2),
0)
entered_trn_le_om_cr,
DECODE (po_trx.gl_event,
'DEBIT', ROUND (po_trx.trn_le_om, 2),
0)
entered_trn_le_om_dr_vat,
DECODE (po_trx.gl_event,
'CREDIT', ROUND (po_trx.trn_le_om, 2),
0)
entered_trn_le_om_cr_vat--witbor end
,
DECODE (po_trx.gl_event,
'DEBIT', ROUND (po_trx.com_expedia, 2),
0)
entered_com_expedia_dr,
DECODE (po_trx.gl_event,
'CREDIT', ROUND (po_trx.com_expedia, 2),
0)
entered_com_expedia_cr,
DECODE (po_trx.gl_event,
'DEBIT', ROUND (po_trx.com_expedia, 2),
0)
entered_com_expedia_dr_vat,
DECODE (po_trx.gl_event,
'CREDIT', ROUND (po_trx.com_expedia, 2),
0)
entered_com_expedia_cr_vat--witbor end
,
po_trx.gl_event,
po_trx.item,
po_trx.start_date,
po_trx.not_reimbursement,
po_trx.vendor_id,
po_trx.vat_rate / 100 vat_rate,
po_trx.creation_date creation_date
FROM ( /* DEBIT Pais Implementado */
SELECT pda.code_combination_id,
poh.attribute5, -----phad.xx_po_prov_status,
poh.org_id,
/* 12.1 Intercompany */
'Y' implemented,
NVL (xep.legal_entity_id, -1) om_legal_entity_id,
poh.segment1 po_number,
xlolv.legal_entity_id po_legal_entity_id,
poh.attribute_category,
poh.segment1 || '-' || poh.reference_num reference,
pol.attribute9 cro_hijo,
gcc.segment1,
NVL (gcc3.segment2, gcc2.segment2) segment2,
NVL (gcc3.segment3, gcc2.segment3) segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gcc.segment9,
gcc.segment10,
gcc.segment11,
'11355' segment2_ic_po, -- G_IC_PO_CTA_DEBE
'0000' segment3_ic_po, -- G_IC_SUB_CTA_D_H
'21261' segment2_ic_om, --G_IC_OM_CTA_DEBE
'0000' segment3_ic_om, -- G_IC_SUB_CTA_D_H
NVL (gcc3.segment2, '21261') segment2_ic_om_ic, --:G_IC_OM_CTA_DEBE
NVL (gcc3.segment3, '0000') segment3_ic_om_ic, -- :G_IC_SUB_CTA_D_H
'DEBIT' gl_event,
poh.po_header_id,
pol.po_line_id,
DECODE (xea.source_id_char1, NULL, 'N', 'Y')
extra_attributes_flag,
pol.attribute1 postventa_id,
pol.attribute2 pnr_number,
poh.currency_code,
DECODE (poh.currency_code,
gl.currency_code, poh.rate_type,
'Corporate')
rate_type,
DECODE (poh.currency_code,
gl.currency_code, TO_DATE (NULL),
poh.rate_date)
rate_date,
NVL (poh.rate_type, 'Corporate') rate_type_om,
NVL (poh.rate_date, poh.start_date) rate_date_om,
xlolv.ledger_id,
phad.xx_po_prov_status,
poh.global_attribute17 currency_code_le_om,
pvs.attribute2 new_model_vendor_flag,
msi.segment1 item,
msi.encumbrance_account encumbrance_account,
poh.start_date start_date,
NVL (pol.attribute12, 'N') not_reimbursement, -- No Reembolsable
poh.vendor_id vendor_id,
-- FGBM para evitar errores de Invalid Number
-- Se pasa a utilizar CANONICAL TO NUMBER
-- ( previ o a hacer un replace para evitar problemas)
-- NVL(TO_NUMBER(pol.attribute14), 0) vat_rate,
NVL (
-- fnd_number.canonical_to_number
(
REPLACE (pol.attribute14, ',', '.')),
0)
vat_rate,
SUM (pol.unit_price * pol.quantity) total,
-- SUM(NVL(TO_NUMBER(poh.global_attribute18), 0)) amount_le_om,
-- SUM(NVL(TO_NUMBER(poh.global_attribute19), 0)) additional_cost_le_om -- service fee
SUM (
NVL (
-- fnd_number.canonical_to_number
(
REPLACE (poh.global_attribute18, ',', '.')),
0))
amount_le_om,
-- SUM(NVL(fnd_number.canonical_to_number(replace(poh.global_attribute19, ',', '.')), 0)) additional_cost_le_om -- service fee
-- FGBM 20-ABR-2016
SUM (
NVL (
-- fnd_number.canonical_to_number
(
REPLACE (xea.attribute6, ',', '.')),
0))
ajuste_costo_le_om,
SUM (
NVL (
--fnd_number.canonical_to_number
(
REPLACE (xea.attribute7, ',', '.')),
0))
fee_le_om,
SUM (
NVL (
--fnd_number.canonical_to_number
(
REPLACE (xea.attribute8, ',', '.')),
0))
descuento_le_om,
SUM (
NVL (
--fnd_number.canonical_to_number
(
REPLACE (xea.attribute9, ',', '.')),
0))
trn_le_om,
SUM (
NVL (
--fnd_number.canonical_to_number
(
REPLACE (xea.attribute10, ',', '.')),
0))
com_expedia,
TRUNC(poh.creation_date) creation_date
FROM po_headers_all poh,
po_lines_all pol,
po_headers_all_dfv phad,
xle_entity_profiles xep,
po_line_locations_all pll,
po_releases_all prl,
ap_supplier_sites_all pvs,
--hr_locations_all hrl,
--mtl_units_of_measure mum,
mtl_system_items_kfv msi,
--hr_all_organization_units_vl hro,
po_line_types_b plt,
--fnd_lookup_values_vl flv,
po_vendors pv,
po_distributions_all pda,
gl_code_combinations gcc,
xle_le_ou_ledger_v xlolv,
gl_ledgers gl,
gl_code_combinations gcc2,
gl_code_combinations gcc3,
mtl_parameters mp-- FGBM 20-ABR-2016
,
xx_all_extra_attributes xea
WHERE poh.po_header_id = pol.po_header_id
and poh.attribute13 = '6617886161250'--'87778156200'--'774232154800'
--- AND poh.authorization_status = 'APPROVED'
AND pol.po_line_id = pll.po_line_id
AND phad.row_id = poh.ROWID
/* AND ( (NVL (:p_value_char1, 'N') = 'N'
AND phad.xx_po_prov_status IS NULL)
OR (NVL (:p_value_char1, 'N') = 'Y'
AND phad.xx_po_prov_status LIKE 'ERROR%'))
*/ -- AND pol.unit_meas_lookup_code
-- = mum.unit_of_measure (+)
AND pll.po_release_id = prl.po_release_id(+)
-- AND hrl.location_id (+) = pll.ship_to_location_id
AND NVL (pll.approved_flag, 'N') = 'Y'
AND NVL (pll.CANCEL_FLAG, 'N') = 'N'
AND POH.pcard_id IS NULL
AND pll.shipment_type IN
('STANDARD', 'BLANKET', 'SCHEDULED')
AND NVL (pll.match_option, 'P') = 'P'
-- AND hro.ORGANIZATION_ID (+) = poh.ORG_ID
AND pol.item_id = msi.inventory_item_id(+)
AND NVL (pll.consigned_flag, 'N') <> 'Y'
AND NVL (msi.ORGANIZATION_ID,
pll.SHIP_TO_ORGANIZATION_ID) =
pll.SHIP_TO_ORGANIZATION_ID
AND plt.line_type_id = pol.line_type_id
-- AND flv.lookup_type (+) = 'MATCH_BASIS'
-- AND flv.lookup_code (+) = nvl(plt.MATCHING_BASIS,'QUANTITY')
AND pv.vendor_id = poh.vendor_id
AND pda.po_header_id = pll.po_header_id
AND pda.po_line_id = pll.po_line_id
AND poh.INTERFACE_SOURCE_CODE = 'XX_PO_PROV'
AND pda.line_location_id = pll.line_location_id
AND pda.code_combination_id = gcc.code_combination_id
AND poh.org_id = xlolv.operating_unit_id
AND xlolv.ledger_id = gl.ledger_id
AND mp.ap_accrual_account = gcc2.code_combination_id
AND mp.organization_id = msi.organization_id
AND NVL (phad.xx_po_legal_entity_om, -1) =
xep.name(+)
AND poh.vendor_site_id = pvs.vendor_site_id
AND poh.vendor_id = pvs.vendor_id
-- FGBM 20-ABR-2016
AND xea.source_table(+) = 'PO_LINES'
AND xea.source_id_char1(+) = TO_CHAR (pol.po_line_id)
AND msi.encumbrance_account =
gcc3.code_combination_id(+)
GROUP BY poh.po_header_id,
poh.attribute5,phad.xx_po_prov_status,
pol.po_line_id,
TRUNC(poh.creation_date),
pda.code_combination_id,
poh.org_id,
poh.attribute_category,
poh.segment1 || '-' || poh.reference_num,
pol.attribute9,
gcc.segment1,
NVL (gcc3.segment2, gcc2.segment2),
NVL (gcc3.segment3, gcc2.segment3),
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gcc.segment9,
gcc.segment10,
gcc.segment11,
'11355',--G_IC_PO_CTA_DEBE,
'0000',--G_IC_SUB_CTA_D_H,
'21261',--G_IC_OM_CTA_DEBE,
--G_IC_SUB_CTA_D_H,
NVL (gcc3.segment2, '21261'),--:G_IC_OM_CTA_DEBE
NVL (gcc3.segment3, '0000' ), --:G_IC_SUB_CTA_D_H
'DEBIT',
DECODE (xea.source_id_char1, NULL, 'N', 'Y'),
pol.attribute1, -- postventa_id,
pol.attribute2, -- pnr_number,
/* 12.1 Intercompany */
'Y',
NVL (xep.legal_entity_id, -1),
poh.segment1,
xlolv.legal_entity_id,
poh.currency_code,
DECODE (poh.currency_code,
gl.currency_code, poh.rate_type,
'Corporate'),
DECODE (poh.currency_code,
gl.currency_code, TO_DATE (NULL),
poh.rate_date),
NVL (poh.rate_type, 'Corporate'),
NVL (poh.rate_date, poh.start_date),
poh.rate_date,
xlolv.ledger_id,
phad.xx_po_prov_status,
poh.global_attribute17,
pvs.attribute2, -- new_model_vendor_flag
msi.segment1,
msi.encumbrance_account,
poh.start_date,
NVL (pol.attribute12, 'N'), -- No Reembolsable
poh.vendor_id,
-- FGBM Error invalid number
-- NVL(TO_NUMBER(pol.attribute14), 0) -- VAT rate
NVL (
---fnd_number.canonical_to_number
(
REPLACE (pol.attribute14, ',', '.')),
0)
union all /* CREDIT Pais Implementado */
SELECT pda.code_combination_id,
poh.attribute5,---phad.xx_po_prov_status,
poh.org_id,
/* 12.1 Intercompany */
'Y' implemented,
NVL (xep.legal_entity_id, -1) om_legal_entity_id,
poh.segment1 po_number,
xlolv.legal_entity_id po_legal_entity_id,
poh.attribute_category,
poh.segment1 || '-' || poh.reference_num,
pol.attribute9 cro_hijo,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gcc.segment9,
gcc.segment10,
gcc.segment11,
'21203' segment2_ic_po, --G_IC_PO_CTA_HABER
'0000' segment3_ic_po, --G_IC_SUB_CTA_D_H
'21296' segment2_ic_om, --G_IC_OM_CTA_HABER
'0000'segment3_ic_om, --G_IC_SUB_CTA_D_H
'21296' segment2_ic_om_ic, --G_IC_OM_CTA_HABER
'0000' segment3_ic_om_ic, --G_IC_SUB_CTA_D_H
'CREDIT' gl_event,
poh.po_header_id,
pol.po_line_id,
DECODE (xea.source_id_char1, NULL, 'N', 'Y')
extra_attributes_flag,
pol.attribute1 postventa_id,
pol.attribute2 pnr_number,
poh.currency_code,
DECODE (poh.currency_code,
gl.currency_code, poh.rate_type,
'Corporate')
rate_type,
DECODE (poh.currency_code,
gl.currency_code, TO_DATE (NULL),
poh.rate_date)
rate_date,
NVL (poh.rate_type, 'Corporate') rate_type_om,
NVL (poh.rate_date, poh.start_date) rate_date_om,
xlolv.ledger_id,
phad.xx_po_prov_status,
poh.global_attribute17 currency_code_le_om,
pvs.attribute2 new_model_vendor_flag,
msi.segment1 item,
msi.encumbrance_account encumbrance_account,
poh.start_date start_date,
NVL (pol.attribute12, 'N') not_reimbursement, -- No Reembolsable
poh.vendor_id vendor_id,
-- NVL(TO_NUMBER(pol.attribute14), 0) vat_rate,
NVL (
--fnd_number.canonical_to_number
(
REPLACE (pol.attribute14, ',', '.')),
0)
vat_rate,
SUM (pol.unit_price * pol.quantity) line_total,
--SUM(NVL(TO_NUMBER(poh.global_attribute18), 0)) amount_le_om,
--SUM(NVL(TO_NUMBER(poh.global_attribute19), 0)) additional_cost_le_om -- service fee
SUM (
NVL (
--fnd_number.canonical_to_number
(
REPLACE (poh.global_attribute18, ',', '.')),
0))
amount_le_om,
-- SUM(NVL(fnd_number.canonical_to_number(replace(poh.global_attribute19, ',', '.')), 0)) additional_cost_le_om -- service fee
-- FGBM 20-ABR-2016
SUM (
NVL (
--fnd_number.canonical_to_number
(
REPLACE (xea.attribute6, ',', '.')),
0))
ajuste_costo_le_om, -- cost
SUM (
NVL (
--fnd_number.canonical_to_number
(
REPLACE (xea.attribute7, ',', '.')),
0))
fee_le_om, -- fee
SUM (
NVL (
--fnd_number.canonical_to_number
(
REPLACE (xea.attribute8, ',', '.')),
0))
descuento_le_om, -- dto
SUM (
NVL (
--fnd_number.canonical_to_number
(
REPLACE (xea.attribute9, ',', '.')),
0))
trn_le_om, -- trn
SUM (
NVL (
---fnd_number.canonical_to_number
(
REPLACE (xea.attribute10, ',', '.')),
0))
com_expedia,
TRUNC(poh.creation_date) creation_date
FROM po_headers_all poh,
po_headers_all_dfv phad,
xle_entity_profiles xep,
po_lines_all pol,
po_line_locations_all pll,
po_releases_all prl,
po_vendor_sites_all pvs,
--hr_locations_all hrl,
--mtl_units_of_measure mum,
mtl_system_items_kfv msi,
--hr_all_organization_units_vl hro,
po_line_types_b plt,
--fnd_lookup_values_vl flv,
po_vendors pv,
po_distributions_all pda,
gl_code_combinations gcc,
xle_le_ou_ledger_v xlolv,
gl_ledgers gl-- FGBM 20-ABR-2016
,
xx_all_extra_attributes xea
WHERE poh.po_header_id = pol.po_header_id
and poh.attribute13 = '6617886161250'--'87778156200'--'774232154800'
AND poh.authorization_status = 'APPROVED'
AND phad.row_id = poh.ROWID
/* AND ( (NVL (:p_value_char1, 'N') = 'N'
AND phad.XX_PO_PROV_STATUS IS NULL)
OR (NVL (:p_value_char1, 'N') = 'Y'
AND phad.XX_PO_PROV_STATUS LIKE 'ERROR%'))
*/ AND pol.po_line_id = PLL.PO_LINE_ID
-- AND pol.unit_meas_lookup_code
-- = mum.unit_of_measure (+)
AND pll.po_release_id = prl.po_release_id(+)
-- AND hrl.location_id (+) = pll.ship_to_location_id
AND NVL (pll.approved_flag, 'N') = 'Y'
AND NVL (pll.CANCEL_FLAG, 'N') = 'N'
AND poh.INTERFACE_SOURCE_CODE = 'XX_PO_PROV'
AND POH.pcard_id IS NULL
AND pll.shipment_type IN
('STANDARD', 'BLANKET', 'SCHEDULED')
AND NVL (pll.match_option, 'P') = 'P'
-- AND hro.ORGANIZATION_ID (+) = poh.ORG_ID
AND pol.item_id = msi.inventory_item_id(+)
AND NVL (pll.consigned_flag, 'N') <> 'Y'
AND NVL (msi.ORGANIZATION_ID,
pll.SHIP_TO_ORGANIZATION_ID) =
pll.SHIP_TO_ORGANIZATION_ID
AND plt.line_type_id = pol.line_type_id
-- AND flv.lookup_type (+) = 'MATCH_BASIS'
-- AND flv.lookup_code (+) = nvl(plt.MATCHING_BASIS,'QUANTITY')
AND pv.vendor_id = poh.vendor_id
AND pda.po_header_id = pll.po_header_id
AND pda.po_line_id = pll.po_line_id
AND pda.line_location_id = pll.line_location_id
AND pda.code_combination_id = gcc.code_combination_id
AND poh.org_id = xlolv.operating_unit_id
AND xlolv.ledger_id = gl.ledger_id
AND NVL (phad.xx_po_legal_entity_om, -1) =
xep.name(+)
AND poh.vendor_site_id = pvs.vendor_site_id
AND poh.vendor_id = pvs.vendor_id
-- FGBM 20-ABR-2016
AND xea.source_table(+) = 'PO_LINES'
AND xea.source_id_char1(+) = TO_CHAR (pol.po_line_id)
GROUP BY poh.po_header_id,poh.attribute5,
pol.po_line_id,
TRUNC(poh.creation_date),
pda.code_combination_id,
poh.org_id,
poh.attribute_category,
poh.segment1 || '-' || poh.reference_num,
pol.attribute9,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gcc.segment9,
gcc.segment10,
gcc.segment11,
'21203',--G_IC_PO_CTA_HABER,
'0000',--G_IC_SUB_CTA_D_H,
'21296',--G_IC_OM_CTA_HABER,
--'0000',--G_IC_SUB_CTA_D_H,
'CREDIT',
DECODE (xea.source_id_char1, NULL, 'N', 'Y'),
pol.attribute1, -- postventa_id
pol.attribute2, -- pnr_number
/* 12.1 Intercompany */
'Y',
NVL (xep.legal_entity_id, -1),
poh.segment1,
xlolv.legal_entity_id,
poh.currency_code,
DECODE (poh.currency_code,
gl.currency_code, poh.rate_type,
'Corporate'),
DECODE (poh.currency_code,
gl.currency_code, TO_DATE (NULL),
poh.rate_date),
NVL (poh.rate_type, 'Corporate'),
NVL (poh.rate_date, poh.start_date),
xlolv.ledger_id,
phad.xx_po_prov_status,
poh.global_attribute17,
pvs.attribute2,
msi.segment1,
msi.encumbrance_account,
poh.start_date,
NVL (pol.attribute12, 'N'), -- No Reembolsable
poh.vendor_id,
-- NVL(TO_NUMBER(pol.attribute14), 0) -- VAT rate
NVL (
--fnd_number.canonical_to_number
(
REPLACE (pol.attribute14, ',', '.')),
0)
)po_trx
Comentarios
Publicar un comentario