-- Consulta registrada el 2026-02-26T19:35:09.393725
select * from PO_ACTION_HISTORY fetch first 10 rows only

-- Consulta registrada el 2026-02-26T18:49:43.070587
SELECT DISTINCT CREATED_BY

FROM PO_HEADERS_ALL H
--WHERE CREATED_BY = 'maximiliano.yanez.y@uai.cl'
FETCH FIRST 100 ROWS ONLY

-- Consulta registrada el 2026-02-26T18:49:22.757194
SELECT  distinct CREATED_BY

FROM PO_HEADERS_ALL H
--WHERE CREATED_BY = 'maximiliano.yanez.y@uai.cl'
FETCH FIRST 100 ROWS ONLY

-- Consulta registrada el 2026-02-26T18:48:38.529974
SELECT

   -- H.CREATION_DATE,
  distinct  H.CREATED_BY
    --H.LAST_UPDATE_DATE,

FROM PO_HEADERS_ALL H
--WHERE CREATED_BY = 'maximiliano.yanez.y@uai.cl'
ORDER BY H.LAST_UPDATE_DATE DESC, H.PO_HEADER_ID DESC
FETCH FIRST 100 ROWS ONLY

-- Consulta registrada el 2026-02-26T18:48:28.335934
SELECT

   -- H.CREATION_DATE,
  distinct  H.CREATED_BY
    --H.LAST_UPDATE_DATE,

FROM PO_HEADERS_ALL H
--WHERE CREATED_BY = 'maximiliano.yanez.y@uai.cl'
ORDER BY H.LAST_UPDATE_DATE DESC, H.PO_HEADER_ID DESC
FETCH FIRST 10 ROWS ONLY

-- Consulta registrada el 2026-02-26T18:47:27.031213
SELECT
    H.PO_HEADER_ID,
    H.PRC_BU_ID,
    H.TYPE_LOOKUP_CODE,
    H.SEGMENT1 AS PO_NUMBER,

    H.DOCUMENT_STATUS,
    H.APPROVED_FLAG,
    H.APPROVED_DATE,
    H.CANCEL_FLAG,
    H.CLOSED_DATE,
    H.REVISION_NUM,

    H.CREATION_DATE,
    H.CREATED_BY,
    H.LAST_UPDATE_DATE,

    H.AGENT_ID,
    H.VENDOR_ID,
    H.VENDOR_SITE_ID,
    H.VENDOR_CONTACT_ID,
    H.VENDOR_ORDER_NUM,

    H.EMAIL_ADDRESS,

    H.SHIP_TO_LOCATION_ID,
    H.BILL_TO_LOCATION_ID,
    H.TERMS_ID,

    H.CURRENCY_CODE,
    H.RATE_TYPE,
    H.RATE_DATE,
    H.RATE,

    H.START_DATE,
    H.END_DATE,
    H.BLANKET_TOTAL_AMOUNT,
    H.AMOUNT_LIMIT,
    H.MIN_RELEASE_AMOUNT,
    H.AMOUNT_RELEASED,

    H.COMMENTS,
    H.NOTE_TO_VENDOR,
    H.NOTE_TO_RECEIVER
FROM PO_HEADERS_ALL H
--WHERE CREATED_BY = 'maximiliano.yanez.y@uai.cl'
ORDER BY H.LAST_UPDATE_DATE DESC, H.PO_HEADER_ID DESC
FETCH FIRST 10 ROWS ONLY

-- Consulta registrada el 2026-02-26T18:44:55.305140
SELECT
    H.PO_HEADER_ID,
    H.PRC_BU_ID,
    H.TYPE_LOOKUP_CODE,
    H.SEGMENT1 AS PO_NUMBER,

    H.DOCUMENT_STATUS,
    H.APPROVED_FLAG,
    H.APPROVED_DATE,
    H.CANCEL_FLAG,
    H.CLOSED_DATE,
    H.REVISION_NUM,

    H.CREATION_DATE,
    H.CREATED_BY,
    H.LAST_UPDATE_DATE,

    H.AGENT_ID,
    H.VENDOR_ID,
    H.VENDOR_SITE_ID,
    H.VENDOR_CONTACT_ID,
    H.VENDOR_ORDER_NUM,

    H.EMAIL_ADDRESS,

    H.SHIP_TO_LOCATION_ID,
    H.BILL_TO_LOCATION_ID,
    H.TERMS_ID,

    H.CURRENCY_CODE,
    H.RATE_TYPE,
    H.RATE_DATE,
    H.RATE,

    H.START_DATE,
    H.END_DATE,
    H.BLANKET_TOTAL_AMOUNT,
    H.AMOUNT_LIMIT,
    H.MIN_RELEASE_AMOUNT,
    H.AMOUNT_RELEASED,

    H.COMMENTS,
    H.NOTE_TO_VENDOR,
    H.NOTE_TO_RECEIVER
FROM PO_HEADERS_ALL H
ORDER BY H.LAST_UPDATE_DATE DESC, H.PO_HEADER_ID DESC
FETCH FIRST 10 ROWS ONLY

-- Consulta registrada el 2026-02-26T18:44:05.799123
SELECT
    RH.REQUISITION_HEADER_ID,
    RH.REQUISITION_NUMBER,
    RH.REQ_BU_ID,
    RH.DOCUMENT_STATUS,
    RH.PREPARER_ID,
    RH.SUBMISSION_DATE,
    RH.CREATED_BY,
    RH.APPROVED_DATE,
    RH.DESCRIPTION AS REQ_DESCRIPTION,

    RL.REQUISITION_LINE_ID,
    RL.LINE_NUMBER,
    RL.ITEM_DESCRIPTION,
    RL.QUANTITY,
    RL.UOM_CODE,
    RL.UNIT_PRICE,
    RL.AMOUNT,
    RL.CURRENCY_AMOUNT,
    RL.NEED_BY_DATE,
    RL.REQUESTER_ID,
    RL.VENDOR_ID,
    RL.VENDOR_SITE_ID,
    RL.REQS_IN_POOL_FLAG
FROM POR_REQUISITION_HEADERS_ALL RH
JOIN POR_REQUISITION_LINES_ALL RL
  ON RL.REQUISITION_HEADER_ID = RH.REQUISITION_HEADER_ID
WHERE RL.PO_HEADER_ID IS NULL
  AND RL.PO_LINE_ID IS NULL
ORDER BY RH.SUBMISSION_DATE DESC, RH.REQUISITION_NUMBER DESC, RL.LINE_NUMBER
FETCH FIRST 10 ROWS ONLY

-- Consulta registrada el 2026-02-26T18:38:03.827455
SELECT
    RH.REQUISITION_HEADER_ID,
    RH.REQUISITION_NUMBER,
    RH.REQ_BU_ID,
    RH.DOCUMENT_STATUS,
    RH.PREPARER_ID,
    RH.SUBMISSION_DATE,
    RH.APPROVED_DATE,
    RH.DESCRIPTION AS REQ_DESCRIPTION,

    RL.REQUISITION_LINE_ID,
    RL.LINE_NUMBER,
    RL.ITEM_DESCRIPTION,
    RL.QUANTITY,
    RL.UOM_CODE,
    RL.UNIT_PRICE,
    RL.AMOUNT,
    RL.CURRENCY_AMOUNT,
    RL.NEED_BY_DATE,
    RL.REQUESTER_ID,
    RL.VENDOR_ID,
    RL.VENDOR_SITE_ID,
    RL.REQS_IN_POOL_FLAG
FROM POR_REQUISITION_HEADERS_ALL RH
JOIN POR_REQUISITION_LINES_ALL RL
  ON RL.REQUISITION_HEADER_ID = RH.REQUISITION_HEADER_ID
WHERE RL.PO_HEADER_ID IS NULL
  AND RL.PO_LINE_ID   IS NULL

FETCH FIRST 10 ROWS ONLY

-- Consulta registrada el 2026-02-26T18:33:58.522271
SELECT
    H.PO_HEADER_ID,                 -- PK
    H.PRC_BU_ID,                    -- BU (clave para unicidad junto a SEGMENT1 + TYPE)
    H.TYPE_LOOKUP_CODE,             -- Tipo documento (Standard/Blanket/Contract/etc)
    H.SEGMENT1        AS PO_NUMBER, -- Número OC

    H.DOCUMENT_STATUS,              -- Estado cabecera
    H.APPROVED_FLAG,
    H.APPROVED_DATE,
    H.CANCEL_FLAG,
    H.CLOSED_DATE,
    H.REVISION_NUM,

    H.CREATION_DATE,
    H.LAST_UPDATE_DATE,

    H.AGENT_ID,                     -- Buyer
    H.VENDOR_ID,                    -- Supplier
    H.VENDOR_SITE_ID,
    H.VENDOR_CONTACT_ID,
    H.VENDOR_ORDER_NUM,             -- N° documento proveedor (si aplica)

    H.SHIP_TO_LOCATION_ID,
    H.BILL_TO_LOCATION_ID,
    H.TERMS_ID,                     -- Payment terms

    H.CURRENCY_CODE,
    H.RATE_TYPE,
    H.RATE_DATE,
    H.RATE,

    H.START_DATE,                   -- útil en BPA/CPA/Contracts (vigencia)
    H.END_DATE,
    H.BLANKET_TOTAL_AMOUNT,         -- BPA/CPA
    H.AMOUNT_LIMIT,                 -- acuerdos
    H.MIN_RELEASE_AMOUNT,
    H.AMOUNT_RELEASED,

    H.COMMENTS,
    H.NOTE_TO_VENDOR,
    H.NOTE_TO_RECEIVER
FROM PO_HEADERS_ALL H

FETCH FIRST 10 ROWS ONLY

-- Consulta registrada el 2026-02-26T18:30:58.624787
SELECT * FROM PO_HEADERS_ALL fetch first 10 rows only

-- Consulta registrada el 2026-02-26T18:29:37.331141
SELECT * FROM PO_HEADERS_DRAFT_ALL fetch first 10 rows only

-- Consulta registrada el 2026-02-26T18:21:03.616078
SELECT * FROM PO_HEADERS_DRAFT_ALL
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2026-02-26T17:26:18.997929
SELECT
    P.PROJECT_ID,
    P.SEGMENT1,
    TL.NAME,
    TL.DESCRIPTION,
    P.PROJECT_TYPE_ID,
    P.CARRYING_OUT_ORGANIZATION_ID,
    P.PROJECT_STATUS_CODE,
    PS.PROJECT_STATUS_NAME,
    COALESCE(PS.PROJECT_STATUS_NAME, P.PROJECT_STATUS_CODE) AS PROJECT_STATUS_DISPLAY,
    P.PROJECT_UNIT_ID,
    P.PROJECT_CATEGORY,
    P.START_DATE,
    P.COMPLETION_DATE,
    P.PROJECT_CURRENCY_CODE,
    PT.PROJECT_TYPE,
    PT.DESCRIPTION
FROM PJF_PROJECTS_ALL_B P
LEFT JOIN PJF_PROJECTS_ALL_TL TL
       ON TL.PROJECT_ID = P.PROJECT_ID
      AND TL.LANGUAGE = 'E'
LEFT JOIN PJF_PROJECT_STATUSES_VL PS
       ON PS.PROJECT_STATUS_CODE = P.PROJECT_STATUS_CODE
LEFT JOIN PJF_PROJECT_TYPES_VL PT
       ON PT.PROJECT_TYPE_ID = P.PROJECT_TYPE_ID
       
WHERE SEGMENT1 = 'CX0001'

-- Consulta registrada el 2026-02-26T17:25:13.093762
SELECT
    P.PROJECT_ID,
    P.SEGMENT1,
    TL.NAME,
    TL.DESCRIPTION,
    P.PROJECT_TYPE_ID,
    P.CARRYING_OUT_ORGANIZATION_ID,
    P.PROJECT_STATUS_CODE,
    PS.PROJECT_STATUS_NAME,
    COALESCE(PS.PROJECT_STATUS_NAME, P.PROJECT_STATUS_CODE) AS PROJECT_STATUS_DISPLAY,
    P.PROJECT_UNIT_ID,
    P.PROJECT_CATEGORY,
    P.START_DATE,
    P.COMPLETION_DATE,
    P.PROJECT_CURRENCY_CODE,
    PT.PROJECT_TYPE,
    PT.DESCRIPTION
FROM PJF_PROJECTS_ALL_B P
LEFT JOIN PJF_PROJECTS_ALL_TL TL
       ON TL.PROJECT_ID = P.PROJECT_ID
      AND TL.LANGUAGE = 'E'
LEFT JOIN PJF_PROJECT_STATUSES_VL PS
       ON PS.PROJECT_STATUS_CODE = P.PROJECT_STATUS_CODE
LEFT JOIN PJF_PROJECT_TYPES_VL PT
       ON PT.PROJECT_TYPE_ID = P.PROJECT_TYPE_ID
       
WHERE SEGMENT1 = 'CX0001'
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2026-02-24T19:55:25.541524
SELECT DISTINCT
       p.project_status_code,
       ps.project_status_name
FROM pjf_projects_all_b p
LEFT JOIN pjf_project_statuses_vl ps
       ON ps.project_status_code = p.project_status_code
ORDER BY 1

-- Consulta registrada el 2026-02-24T19:52:28.987748
SELECT distinct
P.PROJECT_STATUS_CODE
FROM PJF_PROJECTS_ALL_B P
LEFT JOIN PJF_PROJECTS_ALL_TL TL
       ON P.PROJECT_ID = TL.PROJECT_ID
      AND TL.LANGUAGE = 'E'
LEFT JOIN PJF_PROJECT_TYPES_VL PT
       ON P.PROJECT_TYPE_ID = PT.PROJECT_TYPE_ID

-- Consulta registrada el 2026-02-24T19:50:51.145147
select distinct plan_status_code from PJO_PLAN_VERSIONS_B fetch first 10 rows only

-- Consulta registrada el 2026-02-24T19:49:53.322297
select * from PJO_PLAN_VERSIONS_B fetch first 10 rows only

-- Consulta registrada el 2026-02-24T19:49:01.674957
select * from PJO_PLAN_VERSIONS_TL fetch first 10 rows only

-- Consulta registrada el 2026-02-13T22:15:56.527686
SELECT COUNT(DISTINCT aia.invoice_id) cnt_honorarios_mtd
FROM ap_invoices_all aia
JOIN poz_supplier_sites_v pss
  ON pss.vendor_site_id = aia.vendor_site_id
 AND pss.vendor_id      = aia.vendor_id
WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE) + 1
  AND pss.party_site_name = 'HONORARIOS'

-- Consulta registrada el 2026-02-13T22:15:03.329156
-- solo las que hoy siguen sin pago
SELECT COUNT(DISTINCT aia.invoice_id) pendientes
FROM ap_invoices_all aia
JOIN poz_supplier_sites_v pss
  ON pss.vendor_site_id = aia.vendor_site_id
 AND pss.vendor_id      = aia.vendor_id
WHERE aia.creation_date >= DATE '2026-01-01'
  AND aia.creation_date <  TRUNC(SYSDATE)+1
  AND pss.party_site_name = 'HONORARIOS'
  AND NOT EXISTS (
      SELECT 1 FROM ap_invoice_payments_all aipa
      WHERE aipa.invoice_id = aia.invoice_id
  )

-- Consulta registrada el 2026-02-13T22:14:55.193888
SELECT COUNT(DISTINCT aia.invoice_id) total_honorarios_2026
FROM ap_invoices_all aia
JOIN poz_supplier_sites_v pss
  ON pss.vendor_site_id = aia.vendor_site_id
 AND pss.vendor_id      = aia.vendor_id
WHERE aia.creation_date >= DATE '2026-01-01'
  AND aia.creation_date <  TRUNC(SYSDATE)+1
  AND pss.party_site_name = 'HONORARIOS'

-- Consulta registrada el 2026-02-13T22:13:42.281956
SELECT DISTINCT
    ipa.payment_reference_number    AS referencia,
    ipa.payment_id,
    TRUNC(ipa.payment_date)         AS fecha_pago,
    aipa.invoice_id,
    aia.invoice_num,
    TRUNC(aia.creation_date)        AS invoice_creation_date,
    aia.vendor_id,
    aia.vendor_site_id,
    pss.party_site_name             AS payment_site
FROM iby_payments_all ipa
JOIN ap_checks_all aca
  ON aca.payment_id = ipa.payment_id
JOIN ap_invoice_payments_all aipa
  ON aipa.check_id = aca.check_id
JOIN ap_invoices_all aia
  ON aia.invoice_id = aipa.invoice_id
LEFT JOIN poz_supplier_sites_v pss
  ON pss.vendor_site_id = aia.vendor_site_id
 AND pss.vendor_id      = aia.vendor_id
WHERE ipa.payment_reference_number IN (
  12055,12064,12066,12062,12065,12058,12060,12063,12061,12057,12056,12059,12053,12198
)
ORDER BY referencia, aipa.invoice_id

-- Consulta registrada el 2026-02-13T22:09:06.060284
SELECT DISTINCT
    aca.check_number                AS nro_pago,
    ipa.payment_id,
    TRUNC(ipa.payment_date)         AS fecha_pago,
    aipa.invoice_id,
    aia.invoice_num,
    TRUNC(aia.creation_date)        AS invoice_creation_date,
    aia.vendor_id,
    aia.vendor_site_id,
    pss.party_site_name             AS payment_site
FROM ap_checks_all aca
JOIN iby_payments_all ipa
  ON ipa.payment_id = aca.payment_id
JOIN ap_invoice_payments_all aipa
  ON aipa.check_id = aca.check_id
JOIN ap_invoices_all aia
  ON aia.invoice_id = aipa.invoice_id
LEFT JOIN poz_supplier_sites_v pss
  ON pss.vendor_site_id = aia.vendor_site_id
 AND pss.vendor_id      = aia.vendor_id
WHERE aca.check_number IN (
  12186,12055,12064,12066,12062,12065,12058,12060,12063,12061,12057,12056,12059,12053,12054
)
ORDER BY nro_pago, aipa.invoice_id

-- Consulta registrada el 2026-02-13T22:04:09.569035
SELECT
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM ap_invoice_payments_all aipa
      WHERE aipa.invoice_id = aia.invoice_id
    ) THEN 'TIENE_PAGO'

    WHEN EXISTS (
      SELECT 1
      FROM ap_invoice_distributions_all d
      WHERE d.invoice_id = aia.invoice_id
        AND d.line_type_lookup_code = 'PREPAY'
        AND d.reversal_flag = 'N'
    ) THEN 'TIENE_PREPAY'

    ELSE 'SIN_PAGO_NI_PREPAY'
  END estado,
  COUNT(DISTINCT aia.invoice_id) cnt
FROM ap_invoices_all aia
JOIN poz_supplier_sites_v pss
  ON pss.vendor_site_id = aia.vendor_site_id
 AND pss.vendor_id      = aia.vendor_id
WHERE aia.creation_date >= DATE '2026-01-01'
  AND aia.creation_date <  TRUNC(SYSDATE) + 1
  AND pss.party_site_name = 'HONORARIOS'
GROUP BY
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM ap_invoice_payments_all aipa
      WHERE aipa.invoice_id = aia.invoice_id
    ) THEN 'TIENE_PAGO'

    WHEN EXISTS (
      SELECT 1
      FROM ap_invoice_distributions_all d
      WHERE d.invoice_id = aia.invoice_id
        AND d.line_type_lookup_code = 'PREPAY'
        AND d.reversal_flag = 'N'
    ) THEN 'TIENE_PREPAY'

    ELSE 'SIN_PAGO_NI_PREPAY'
  END
ORDER BY cnt DESC

-- Consulta registrada el 2026-02-13T22:03:13.939191
SELECT
  CASE
    WHEN EXISTS (SELECT 1 FROM ap_invoice_payments_all aipa
                 WHERE aipa.invoice_id = aia.invoice_id)
      THEN 'TIENE_PAGO'
    WHEN EXISTS (SELECT 1 FROM ap_invoice_distributions_all d
                 WHERE d.invoice_id = aia.invoice_id
                   AND d.line_type_lookup_code = 'PREPAY'
                   AND d.reversal_flag = 'N')
      THEN 'TIENE_PREPAY'
    ELSE 'SIN_PAGO_NI_PREPAY'
  END estado,
  COUNT(*) cnt
FROM ap_invoices_all aia
JOIN poz_supplier_sites_v pss
  ON pss.vendor_site_id = aia.vendor_site_id
 AND pss.vendor_id      = aia.vendor_id
WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE)+1
  AND pss.party_site_name = 'HONORARIOS'
GROUP BY
  CASE
    WHEN EXISTS (SELECT 1 FROM ap_invoice_payments_all aipa
                 WHERE aipa.invoice_id = aia.invoice_id)
      THEN 'TIENE_PAGO'
    WHEN EXISTS (SELECT 1 FROM ap_invoice_distributions_all d
                 WHERE d.invoice_id = aia.invoice_id
                   AND d.line_type_lookup_code = 'PREPAY'
                   AND d.reversal_flag = 'N')
      THEN 'TIENE_PREPAY'
    ELSE 'SIN_PAGO_NI_PREPAY'
  END

-- Consulta registrada el 2026-02-13T22:00:26.938042
SELECT
  (SELECT COUNT(DISTINCT aia.invoice_id)
   FROM ap_invoices_all aia
   WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
     AND aia.creation_date <  TRUNC(SYSDATE)+1) AS total_mtd,

  (SELECT COUNT(DISTINCT aia.invoice_id)
   FROM ap_invoices_all aia
   JOIN poz_supplier_sites_v pss
     ON pss.vendor_site_id = aia.vendor_site_id
    AND pss.vendor_id      = aia.vendor_id
   WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
     AND aia.creation_date <  TRUNC(SYSDATE)+1
     AND pss.party_site_name = 'HONORARIOS') AS honorarios_mtd,

  (SELECT COUNT(DISTINCT aia.invoice_id)
   FROM ap_invoices_all aia
   JOIN poz_supplier_sites_v pss
     ON pss.vendor_site_id = aia.vendor_site_id
    AND pss.vendor_id      = aia.vendor_id
   JOIN ap_invoice_payments_all aipa
     ON aipa.invoice_id = aia.invoice_id
   JOIN ap_checks_all aca
     ON aca.check_id = aipa.check_id
   JOIN iby_payments_all ipa
     ON ipa.payment_id = aca.payment_id
   WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
     AND aia.creation_date <  TRUNC(SYSDATE)+1
     AND pss.party_site_name = 'HONORARIOS') AS honorarios_mtd_con_pago
FROM dual

-- Consulta registrada el 2026-02-13T22:00:11.864570
SELECT COUNT(DISTINCT aia.invoice_id) cnt_honorarios_mtd_con_prepay
FROM ap_invoices_all aia
JOIN poz_supplier_sites_v pss
  ON pss.vendor_site_id = aia.vendor_site_id
 AND pss.vendor_id      = aia.vendor_id
JOIN ap_invoice_distributions_all prepay
  ON prepay.invoice_id = aia.invoice_id
 AND prepay.line_type_lookup_code = 'PREPAY'
 AND prepay.reversal_flag = 'N'
WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE) + 1
  AND pss.party_site_name = 'HONORARIOS'

-- Consulta registrada el 2026-02-13T21:59:57.389445
SELECT COUNT(DISTINCT aia.invoice_id) cnt_honorarios_mtd_con_pago
FROM ap_invoices_all aia
JOIN poz_supplier_sites_v pss
  ON pss.vendor_site_id = aia.vendor_site_id
 AND pss.vendor_id      = aia.vendor_id
JOIN ap_invoice_payments_all aipa
  ON aipa.invoice_id = aia.invoice_id
JOIN ap_checks_all aca
  ON aca.check_id = aipa.check_id
JOIN iby_payments_all ipa
  ON ipa.payment_id = aca.payment_id
WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE) + 1
  AND pss.party_site_name = 'HONORARIOS'

-- Consulta registrada el 2026-02-13T21:59:38.358467
SELECT COUNT(DISTINCT aia.invoice_id) cnt_honorarios_mtd
FROM ap_invoices_all aia
JOIN poz_supplier_sites_v pss
  ON pss.vendor_site_id = aia.vendor_site_id
 AND pss.vendor_id      = aia.vendor_id
WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE) + 1
  AND pss.party_site_name = 'HONORARIOS'

-- Consulta registrada el 2026-02-13T21:58:48.913354
SELECT COUNT(DISTINCT aia.invoice_id) cnt
FROM ap_invoices_all aia
JOIN poz_supplier_sites_v pss
  ON pss.vendor_site_id = aia.vendor_site_id
 AND pss.vendor_id      = aia.vendor_id
WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE)+1
  AND pss.party_site_name = 'HONORARIOS'

-- Consulta registrada el 2026-02-13T21:57:43.366725
SELECT COUNT(DISTINCT aia.invoice_id) facturas_mtd_con_distrib
FROM ap_invoices_all aia
JOIN ap_invoice_distributions_all apd
  ON apd.invoice_id = aia.invoice_id
WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE)+1
  AND apd.reversal_flag='N'
  AND apd.line_type_lookup_code IN ('ITEM','ACCRUAL')
  AND apd.distribution_line_number = 1

-- Consulta registrada el 2026-02-13T21:57:37.511023
SELECT COUNT(DISTINCT aia.invoice_id) facturas_mtd
FROM ap_invoices_all aia
WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE)+1

-- Consulta registrada el 2026-02-13T21:56:08.036712
SELECT
  COUNT(*) total_mtd,
  SUM(CASE WHEN pss.vendor_site_id IS NOT NULL THEN 1 ELSE 0 END) con_site,
  SUM(CASE WHEN pss.vendor_site_id IS NOT NULL
            AND pss.vendor_id = aia.vendor_id THEN 1 ELSE 0 END) con_site_y_vendor_ok
FROM ap_invoices_all aia
LEFT JOIN poz_supplier_sites_v pss
  ON aia.vendor_site_id = pss.vendor_site_id
WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE)+1

-- Consulta registrada el 2026-02-13T21:53:42.914198
SELECT
    aia.invoice_id,
    aia.invoice_num,
    aia.creation_date,
    aia.vendor_id,
    aia.vendor_site_id,
    pss.party_site_name AS payment_site
FROM ap_invoices_all aia
LEFT JOIN poz_supplier_sites_v pss
       ON aia.vendor_site_id = pss.vendor_site_id
WHERE 1=1
  AND aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE)+1
ORDER BY aia.creation_date DESC

-- Consulta registrada el 2026-02-13T16:15:52.462454
SELECT DISTINCT pss.party_site_name
FROM ap_invoices_all aia
JOIN poz_supplier_sites_v pss
     ON aia.vendor_site_id = pss.vendor_site_id
WHERE aia.creation_date >= TRUNC(SYSDATE,'MM')
ORDER BY 1

-- Consulta registrada el 2026-02-13T16:13:41.696828
SELECT
    aia.invoice_id,
    aia.invoice_num,
    aia.creation_date,
    pss.party_site_name
FROM ap_invoices_all aia
JOIN poz_supplier_sites_v pss
     ON aia.vendor_site_id = pss.vendor_site_id
WHERE pss.party_site_name = 'HONORARIOS'
  AND aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE)+1
ORDER BY aia.creation_date DESC

-- Consulta registrada el 2026-02-13T16:08:21.061499
SELECT DISTINCT
       pss.party_site_name AS payment_site
FROM ap_invoices_all aia
LEFT JOIN poz_supplier_sites_v pss
       ON aia.vendor_site_id = pss.vendor_site_id
ORDER BY payment_site

-- Consulta registrada el 2026-02-13T16:05:50.110066
SELECT
    aia.invoice_id,
    aia.invoice_num,
    aia.creation_date,
    aia.vendor_id,
    aia.vendor_site_id,
    pss.party_site_name AS payment_site
FROM ap_invoices_all aia
LEFT JOIN poz_supplier_sites_v pss
       ON aia.vendor_site_id = pss.vendor_site_id
WHERE 1=1
  AND aia.creation_date >= TRUNC(SYSDATE,'MM')
  AND aia.creation_date <  TRUNC(SYSDATE)+1
ORDER BY aia.creation_date DESC

-- Consulta registrada el 2026-02-13T11:08:32.455586
select * from POZ_SUPPLIER_SITES_V
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2026-02-13T11:06:29.575303
select TRUNC(sysdate,'MM') from dual --POZ_SUPPLIER_SITES_V
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2026-02-13T10:54:46.644282
select * from POZ_SUPPLIER_SITES_V
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2026-02-13T10:41:56.008811
select * from poz_suppliers_pii
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2026-02-13T10:40:46.787664
select * from poz_suppliers_v
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2026-02-12T18:31:19.734027
SELECT 
  LOOKUP_TYPE,
  LOOKUP_CODE,
  MEANING,
  DESCRIPTION,
  TAG as PRICE,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type IN (
  'UAI_HNU_PRIVILEGE_TO_SKU'
)
AND enabled_flag = 'Y'

-- Consulta registrada el 2026-02-12T18:30:38.826464
SELECT 
  LOOKUP_TYPE,
  LOOKUP_CODE,
  MEANING,
  DESCRIPTION,
  TAG as PRICE,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type IN (
  'UAI_HNU_SKU_LIST'
)
AND enabled_flag = 'Y'

-- Consulta registrada el 2026-02-12T18:30:13.945401
SELECT 
  LOOKUP_TYPE,
  LOOKUP_CODE,
  MEANING,
  DESCRIPTION,
  TAG as PRICE,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type IN (
  'UAI_HNU_PRIVILEGE_TO_SKU'
)
AND enabled_flag = 'Y'

-- Consulta registrada el 2026-02-12T18:27:58.599071
SELECT 
  LOOKUP_TYPE,
  LOOKUP_CODE,
  MEANING,
  DESCRIPTION,
  TAG as PRICE,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type IN (
  'UAI_HNU_SKU_LIST'
)
AND enabled_flag = 'Y'

-- Consulta registrada el 2026-02-11T15:35:37.601326
SELECT
    ORGANIZATION_ID,
    NAME
FROM
    HR_ORGANIZATION_UNITS
FETCH FIRST 999 ROWS ONLY

-- Consulta registrada el 2026-02-11T15:32:35.209310
SELECT
    PROJECT_ID,
    PROJECT_CATEGORY,
    ATTRIBUTE1,
    ATTRIBUTE2,
    ATTRIBUTE3,
    ATTRIBUTE4,
    ATTRIBUTE5
FROM
    PJF_PROJECTS_ALL_B
WHERE
    ROWNUM <= 50

-- Consulta registrada el 2026-02-11T15:21:04.795498
SELECT DISTINCT
    P.PROJECT_STATUS_CODE
FROM
    PJF_PROJECTS_ALL_B P
ORDER BY
    P.PROJECT_STATUS_CODE

-- Consulta registrada el 2026-02-11T15:18:22.268029
SELECT
        P.*,
        TL.*,
        PT.*
FROM
        PJF_PROJECTS_ALL_B P
        LEFT JOIN PJF_PROJECTS_ALL_TL TL
            ON TL.PROJECT_ID = P.PROJECT_ID
           AND TL.LANGUAGE = 'E'
        LEFT JOIN PJF_PROJECT_TYPES_VL PT
            ON PT.PROJECT_TYPE_ID = P.PROJECT_TYPE_ID
fetch first 10 rows only

-- Consulta registrada el 2026-02-10T13:35:54.498126
--select * from egp_system_items where ITEM_NUMBER = '29019-A' fetch first 100 rows only

--select * from gl_code_combinations where code_combination_id = '64164' fetch first 100 rows only

select * from gl_daily_rates ORDER BY CONVERSION_DATE DESC

-- Consulta registrada el 2026-02-10T13:34:58.073432
--select * from egp_system_items where ITEM_NUMBER = '29019-A' fetch first 100 rows only

--select * from gl_code_combinations where code_combination_id = '64164' fetch first 100 rows only

select * from gl_daily_rates

-- Consulta registrada el 2026-02-10T13:29:42.283289
--select * from egp_system_items where ITEM_NUMBER = '29019-A' fetch first 100 rows only

select * from gl_code_combinations where code_combination_id = '64164' fetch first 100 rows only

-- Consulta registrada el 2026-02-10T13:29:08.533514
--select * from egp_system_items where ITEM_NUMBER = '29019-A' fetch first 100 rows only

select * from gl_code_combinations fetch first 100 rows only

-- Consulta registrada el 2026-02-10T13:28:05.984967
select * from egp_system_items where ITEM_NUMBER = '29019-A' fetch first 100 rows only

-- Consulta registrada el 2026-02-10T13:26:58.685553
select * from egp_system_items fetch first 100 rows only

-- Consulta registrada el 2026-02-10T13:08:52.553001
SELECT d.*
  FROM vrm_source_documents d
  WHERE d.DATA_TRANSFORMATION_STATUS = 'NOT_PROCESSED'
  
fetch first 10 rows only

-- Consulta registrada el 2026-02-10T12:46:59.811419
SELECT d.*
  FROM vrm_source_documents d
  WHERE d.BILL_TO_CUSTOMER_PARTY_NUM = '20156700'
  
fetch first 10 rows only

-- Consulta registrada el 2026-02-10T12:46:18.129712
SELECT d.*
  FROM vrm_source_documents d
  WHERE d.data_transformation_status <> 'NOT_PROCESSED'
fetch first 10 rows only

-- Consulta registrada el 2026-02-10T12:38:09.463337
SELECT d.*
  FROM vrm_source_documents d
  WHERE d.data_transformation_status <> 'NOT_PROCESSED' fetch first 10 rows only

-- Consulta registrada el 2026-02-10T12:13:11.363034
select * from EGP_CATEGORY_SETS_B fetch first 10 rows only

-- Consulta registrada el 2026-02-10T12:12:56.579323
select * from EGP_ITEM_CATEGORIES fetch first 10 rows only

-- Consulta registrada el 2026-02-10T12:12:16.381197
select * from EGO_ITEM_EFF_B fetch first 10 rows only

-- Consulta registrada el 2026-02-10T12:10:12.017942
select * from EGP_SYSTEM_ITEMS fetch first 10 rows only

-- Consulta registrada el 2026-02-10T12:09:16.625535
select * from VRM_SOURCE_DOC_LINES fetch first 10 rows only

-- Consulta registrada el 2026-02-10T12:05:13.501139
select * from VRM_SOURCE_DOCUMENTS fetch first 10 rows only

-- Consulta registrada el 2026-02-09T17:23:42.807481
select *
from xcc_balance_activities
where CONTROL_BUDGET_ID in ('300000005451355', '300000004958420')


fetch first 10 rows only

-- Consulta registrada el 2026-02-09T17:20:34.861529
select *
from xcc_balance_activities
where CONTROL_BUDGET_ID = '300000005451355'

fetch first 10 rows only

-- Consulta registrada el 2026-02-09T17:19:11.874050
select *
from xcc_balance_activities
where CONTROL_BUDGET_ID = '300000004958420'

fetch first 10 rows only

-- Consulta registrada el 2026-02-09T17:18:40.754711
select *
from xcc_balance_activities

fetch first 10 rows only

-- Consulta registrada el 2026-02-09T17:11:41.735290
select CONTROL_BUDGET_ID, NAME, DESCRIPTION
from xcc_control_budgets

fetch first 10 rows only

-- Consulta registrada el 2026-02-09T13:16:49.087085
WITH role_hierarchy AS (
    -- Construcción de jerarquaa de roles
    SELECT 
        CONNECT_BY_ROOT parent_role_code AS top_role_code,
        CONNECT_BY_ROOT parent_role_id AS top_role_id,
        child_role_id AS role_id
    FROM (
        SELECT 
            pr.code AS parent_role_code,
            pr.role_id AS parent_role_id,
            cr.role_id AS child_role_id
        FROM 
            fusion.ase_role_b pr
            INNER JOIN fusion.ase_role_role_mbr rr ON pr.role_id = rr.child_role_id AND rr.effective_end_date IS NULL
            INNER JOIN fusion.ase_role_b cr ON cr.role_id = rr.parent_role_id
    )
    CONNECT BY NOCYCLE PRIOR child_role_id = parent_role_id
    UNION ALL
    -- Roles sin jerarquia son su propio top_role traerse la columna a modo de validación
    SELECT 
        r.code AS top_role_code,
        r.role_id AS top_role_id,
        r.role_id AS role_id
    FROM fusion.ase_role_b r
),
privilege_service AS (
    -- privilegios con servicios
    SELECT /*+ MATERIALIZE */
        privrole.role_id,
        priv.code,
        privtl.name,
        privrole.effective_start_date AS policy_start_date,
        privrole.effective_end_date AS policy_end_date,
        metric.metric_code AS service_id,
        metric.metric_name AS service
    FROM 
        fusion.ase_priv_role_mbr privrole
        INNER JOIN fusion.ase_privilege_b priv ON priv.privilege_id = privrole.privilege_id
        INNER JOIN fusion.ase_privilege_tl privtl ON priv.privilege_id = privtl.privilege_id AND privtl.Language = 'US'
        INNER JOIN (
            -- los privilegios y metricas
            SELECT 
                vs.value_set_code AS metric_code,
                vl.translated_value AS priv_code
            FROM 
                fnd_vs_value_sets vs
                INNER JOIN fnd_vs_values_b v ON v.value_set_id = vs.value_set_id AND v.enabled_flag = 'Y'
                INNER JOIN fnd_vs_values_tl vl ON vl.value_id = v.value_id AND vl.language = 'US'
            WHERE 
                SYSDATE BETWEEN NVL(v.start_date_active, SYSDATE) AND NVL(v.end_date_active, SYSDATE)
        ) metricpriv ON priv.code = metricpriv.priv_code
        INNER JOIN (
            -- las metricas pero con nombres
            SELECT 
                vs.value_set_code AS metric_set_code,
                vl.translated_value AS metric_code,
                vl.description AS metric_name
            FROM 
                fnd_vs_value_sets vs
                INNER JOIN fnd_vs_values_b v ON v.value_set_id = vs.value_set_id AND v.enabled_flag = 'Y'
                INNER JOIN fnd_vs_values_tl vl ON vl.value_id = v.value_id AND vl.language = 'US'
            WHERE 
                v.flex_value_attribute17 IS NULL
                AND SYSDATE BETWEEN NVL(v.start_date_active, SYSDATE) AND NVL(v.end_date_active, SYSDATE)
        ) metric ON metricpriv.metric_code = metric.metric_code
        INNER JOIN (
            -- aca voy hacer las metricas
            SELECT 
                vl.translated_value AS metric_set_code
            FROM 
                fnd_vs_value_sets vs
                INNER JOIN fnd_vs_values_b v ON v.value_set_id = vs.value_set_id AND v.enabled_flag = 'Y'
                INNER JOIN fnd_vs_values_tl vl ON vl.value_id = v.value_id AND vl.language = 'US'
            WHERE 
                vs.value_set_code = 'ORA_PER_METRICS_SETS'
                AND SYSDATE BETWEEN NVL(v.start_date_active, SYSDATE) AND NVL(v.end_date_active, SYSDATE)
        ) metricset ON metric.metric_set_code = metricset.metric_set_code
    WHERE
        -- Filtros de los servicio que se excluyeron
        metric.metric_name NOT LIKE 'SUB METRIC%'
        AND metric.metric_name NOT LIKE 'Cobrowse%'
        AND metric.metric_name NOT LIKE 'PRM%'
        AND metric.metric_name NOT LIKE 'Oracle DataFox%'
        AND metric.metric_name NOT LIKE 'CHAT%'
        AND metric.metric_name NOT LIKE 'CRM%'
        AND metric.metric_name NOT LIKE 'Channel%'
)
SELECT DISTINCT 
    ps.service_id,
    ps.service,
    u.user_login,
    ps.code AS privilege_code,
    ps.name AS privilege_name,
    rh.top_role_id,
    rtl.role_name,
    rh.top_role_code
FROM 
    fusion.ase_user_role_mbr ur
    INNER JOIN fusion.ase_user_b u ON ur.user_id = u.user_id
    INNER JOIN fusion.per_users pu ON u.user_guid = pu.user_guid AND pu.active_flag = 'Y' AND NVL(pu.suspended, 'N') = 'N'
    INNER JOIN fusion.ase_role_tl rtl ON rtl.role_id = ur.role_id AND rtl.language = 'US'
    INNER JOIN role_hierarchy rh ON rh.top_role_id = ur.role_id
    INNER JOIN privilege_service ps ON ps.role_id = rh.role_id
WHERE 
    NVL(ur.effective_end_date, SYSDATE) >= SYSDATE
    AND ps.policy_start_date <= NVL(ur.effective_end_date, SYSDATE)
    AND NVL(ps.policy_end_date, SYSDATE) >= SYSDATE
    AND u.user_login NOT IN (
        'orcladmin', 'weblogic_read_only', 'xelsysadm', 'oamSoftwareUser',
        'IDROUser', 'PolicyROUser', 'PolicyRWUser', 'oimAdminUser',
        'IDMPolicyROUser', 'IDMPolicyRWUser', 'faoperator', 'saas_readonly',
        'em_monitoring', 'oamAdminUser', 'weblogic_idm', 'IDRWUser',
        'PUBLIC', 'FAAdmin', 'weblogic', 'XELOPERATOR', 'OblixAnonymous',
        'FAWService', 'OCLOUD9_osn_APPID'
    )
    AND u.user_login NOT LIKE '%_APPID'
ORDER BY 
    ps.service, 
    u.user_login

-- Consulta registrada el 2026-02-09T02:12:10.058245
SELECT COUNT(*) AS total_rows
FROM (
WITH 
  gestion_assig as (
     select MEANING, ATTRIBUTE1 forma, ATTRIBUTE2 cuenta, ATTRIBUTE3 cons from FND_LOOKUP_VALUES_VL where LOOKUP_TYPE='UAI_CONS_GESTION_ASIGNACION' 
  ),
  coscen as (
    select v.flex_value, v.ATTRIBUTE1 EsCentro, v.ATTRIBUTE2 TipoArea, 
                         v.ATTRIBUTE3 || ' ' || (select f.DESCRIPTION from fnd_flex_values_vl f where f.VALUE_CATEGORY='Area' and f.FLEX_VALUE=v.ATTRIBUTE3) Area,
                         v.ATTRIBUTE4 || ' ' || (select f.DESCRIPTION from fnd_flex_values_vl f where f.VALUE_CATEGORY='Subarea' and f.FLEX_VALUE=v.ATTRIBUTE4) Subarea,
                         v.ATTRIBUTE5 TipoPrograma, v.ATTRIBUTE6 SubtipoPrograma, v.ATTRIBUTE7 CodigoSede, v.ATTRIBUTE12 FormaCons
    from fnd_flex_values_vl v 
    where VALUE_CATEGORY='Centro de Costo UAI'
  ),
  cmr_xla_rcp as (
     SELECT cre.accounting_event_id accounting_event_id
        , DECODE(cre.event_type_code,'EXPENSE_ADJUSTMENT', -1 , cre.cmr_rcv_transaction_id) cmr_rcv_transaction_id
        , cre.cmr_po_distribution_id cmr_po_distribution_id
        , txntypes.transaction_type_name transaction_type
        , cre.transaction_date transaction_date
        , cpod.po_number po_number
        , (select RECEIPT_NUMBER from cmr_rcv_transactions where TRANSACTION_TYPE in ('DELIVER') and PO_DISTRIBUTION_ID=cpod.EXTERNAL_SYSTEM_REF_ID and rownum=1) receipt_number
        , cpod.line_number po_line_number
        , cpod.distribution_number po_distribution_number
        , cpod.vendor_id vendor_id
        , ps.vendor_name vendor_name
        , pss.vendor_site_code vendor_site_code
        , DECODE(event_type_code,'EXPENSE_ADJUSTMENT',NULL,cre.source_doc_qty) quantity
        , DECODE(event_type_code,'EXPENSE_ADJUSTMENT',NULL,iuom.unit_of_measure) uom_code
        , cpod.price + nvl(cpod.nonrecoverable_tax,0) po_price
        , cpod.currency_code currency_code
        , cre.inventory_item_id inventory_item_id
        , esi.item_number item_number
        , cre.inventory_org_id inventory_organization_id
        , iop.organization_code inv_org_code
        , cre.business_unit_id business_unit_id
        , haous.name business_unit_name
       FROM fusion.cmr_purchase_order_dtls cpod
          , fusion.egp_system_items_vl esi
          , fusion.inv_org_parameters iop
          , fusion.hr_all_organization_units haous
          , fusion.poz_suppliers_v ps
          , fusion.poz_supplier_sites_v pss
          , fusion.cmr_rcv_events cre
          , fusion.inv_units_of_measure_vl iuom
          , FUSION.cmr_transaction_types_vl txntypes
        WHERE cre.cmr_po_distribution_id = cpod.cmr_po_distribution_id
          AND esi.inventory_item_id(+) = cre.inventory_item_id
          AND cre.inventory_org_id = esi.organization_id(+)
          AND cre.inventory_org_id = iop.organization_id
          AND haous.organization_id = cre.business_unit_id
          AND cpod.vendor_id = ps.vendor_id
          AND cpod.vendor_site_id = pss.vendor_site_id
          AND cpod.active_flag = 'Y'
          AND cre.cmr_po_distribution_id = cpod.cmr_po_distribution_id
          AND cre.event_type_code not in ('RETRO_PRICE_ADJUSTMENT')
          AND cre.transaction_type_code = txntypes.transaction_type_code
          AND iuom.uom_code (+) = cre.source_doc_uom_code
  ),
  inventory as (
     SELECT cit.external_system_reference
           , cit.subinventory_code
           , clc.cost_source
           , ct.TXN_SOURCE_REF_DOC_NUMBER
           , sum(clc.quantity) quantity
           , clc.uom_code
           , clc.unit_cost
           , cdl.AE_HEADER_ID
           , cdl.AE_LINE_NUM
           , cdl.ENTERED_CURRENCY_AMOUNT
           , itm.ITEM_NUMBER
           , itm.description
           , s.ORGANIZATION_CODE
           , org.ORGANIZATION_NAME
           , ity.TRANSACTION_TYPE_NAME
        FROM cst_layer_costs clc
           , cst_transactions ct
           , cst_inv_transactions cit
           , cst_cost_distribution_lines cdl
           , cst_cost_distributions ccd
           , EGP_SYSTEM_ITEMS itm
           , INV_ORG_PARAMETERS s
           , XLE_ASSOC_BUSINESS_ENTITIES_V org
           , inv_transaction_types_vl ity
       WHERE ct.cst_inv_transaction_id = cit.cst_inv_transaction_id
         AND clc.transaction_id = ct.transaction_id
         AND ccd.transaction_id = ct.transaction_id
         and cdl.distribution_id = ccd.distribution_id
         and ct.INVENTORY_ITEM_ID = itm.INVENTORY_ITEM_ID
         and ct.INVENTORY_ORG_ID = itm.ORGANIZATION_ID
         and ct.INVENTORY_ORG_ID = s.ORGANIZATION_ID
         and ct.INVENTORY_ORG_ID = org.ORGANIZATION_ID
         and ity.TRANSACTION_TYPE_ID = cit.BASE_TXN_TYPE_ID
    group by cit.external_system_reference
           , cit.subinventory_code
           , clc.cost_source
           , ct.TXN_SOURCE_REF_DOC_NUMBER
           , clc.uom_code
           , clc.unit_cost
           , cdl.AE_HEADER_ID
           , cdl.AE_LINE_NUM
           , cdl.ENTERED_CURRENCY_AMOUNT
           , itm.ITEM_NUMBER
           , itm.description
           , s.ORGANIZATION_CODE
           , org.ORGANIZATION_NAME
           , ity.TRANSACTION_TYPE_NAME
   ),
   pjc_cost as (
      SELECT  EXP.expenditure_item_id,
              pp.project_id ,
              pp.segment1 PROJECT_NUMBER,
              pt.proj_element_id task_id,
              pt.element_number TASK_NUMBER,
              et.expenditure_type_id ,
              et.expenditure_type_name EXPENDITURE_TYPE, 
              syslink.meaning expenditure_type_class ,
              et.expenditure_category_id ,
              exp_cat.expenditure_category_name EXPENDITURE_CATEGORY, 
              EXP.expenditure_item_date ,
              HR_ORG.name EXP_ORGANIZATION_NAME, 
              EXP.non_labor_resource_id,
              non_lab_res.nlr_name non_labor_resource,
              TXN_SRC.TRANSACTION_SOURCE Transaction_source, 
              TXN_DOC.DOCUMENT_NAME Document_name , 
              TXN_DOC_ENTRY.DOC_ENTRY_NAME Document_entry_Name,
              ORIG_TRANSACTION_REFERENCE
         FROM pjc_exp_items_all EXP ,
              pjf_proj_elements_b pt ,
              pjf_projects_all_b pp ,
              pjf_exp_types_vl et ,
              pjf_system_linkages_vl syslink,
              pjf_exp_categories_vl exp_cat,
              PJF_NON_LABOR_RES_VL non_lab_res,
              PJF_TXN_SOURCES_VL TXN_SRC,
              pjf_txn_document_vl TXN_DOC,
              PJF_TXN_DOC_ENTRY_VL TXN_DOC_ENTRY,
              HR_ALL_ORGANIZATION_UNITS_VL HR_ORG
         WHERE EXP.project_id = pt.project_id
              AND EXP.project_id = pp.project_id
              AND EXP.task_id = pt.proj_element_id
              AND EXP.expenditure_type_id = et.expenditure_type_id
              AND EXP.system_linkage_function = syslink.FUNCTION
              and et.expenditure_category_id = exp_cat.expenditure_category_id
              and exp.non_labor_resource_id = non_lab_res.non_labor_resource_id(+)
              AND EXP.TRANSACTION_SOURCE_ID =TXN_SRC.TRANSACTION_SOURCE_ID
              AND TXN_DOC.DOCUMENT_ID = EXP.DOCUMENT_ID
              AND TXN_DOC_ENTRY.DOC_ENTRY_ID = EXP.DOC_ENTRY_ID
              and EXP.Expenditure_organization_id = HR_ORG.ORGANIZATION_ID
  ),
   party_num as ( 
      select PARTY_ID, PARTY_NAME, nvl(INCOME_TAX_ID, nvl(ACCOUNT_NUMBER, PERSON_NUMBER)) PARTY_NUMBER from
      (
        select distinct ai.PARTY_ID, PARTY_NAME,
               (select ACCOUNT_NUMBER from HZ_CUST_ACCOUNTS a where a.PARTY_ID=hp.PARTY_ID) ACCOUNT_NUMBER,
               (select INCOME_TAX_ID from POZ_SUPPLIERS_PII a, POZ_SUPPLIERS_V s where S.VENDOR_ID = a.VENDOR_ID and s.PARTY_ID=hp.PARTY_ID) INCOME_TAX_ID,
               (select s.person_number from Per_all_people_f s where s.person_id=ai.PAID_ON_BEHALF_EMPLOYEE_ID and sysdate between s.effective_start_date and s.effective_end_date) person_number
        from AP_INVOICES_ALL ai, hz_parties hp
        where ai.PARTY_ID=hp.PARTY_ID 
      )
  ),
  po_rcv as (
    SELECT ACCOUNTING_EVENT_ID, TRANSACTION_TYPE, PO_NUMBER, (select p.INCOME_TAX_ID from POZ_SUPPLIERS_PII p where p.VENDOR_ID=v.VENDOR_ID) INCOME_TAX_ID, 
           VENDOR_NAME, INV_ORG_CODE, 'Orden Compra' SOURCE_DOCUMENT_TYPE, ITEM_NUMBER, PO_LINE_NUMBER, RECEIPT_NUMBER,
          (SELECT b.description FROM EGP_SYSTEM_ITEMS b where b.ORGANIZATION_ID in (select distinct master_organization_id from INV_ORG_PARAMETERS s) and b.item_number=v.ITEM_NUMBER) ITEM_DESC
    FROM cmr_xla_rcp v  
    union all
    SELECT ACCOUNTING_EVENT_ID, TRANSACTION_TYPE, 'Recepcion: '||RECEIPT_NUMBER||' / Guia: '||SOURCE_DOCUMENT_NUMBER, null VENDOR_ID, 
           'Desde: '||(select ORGANIZATION_CODE from INV_ORG_PARAMETERS s where s.organization_id=FROM_INV_ORGANIZATION_ID) SOURCE_DOCUMENT_NUMBER, 
           INV_ORG_CODE, 'Transferencia entre organizaciones' SOURCE_DOCUMENT_TYPE, ITEM_NUMBER, null PO_LINE_NUMBER, RECEIPT_NUMBER,
          (SELECT b.description FROM EGP_SYSTEM_ITEMS b where b.ORGANIZATION_ID in (select distinct master_organization_id from INV_ORG_PARAMETERS s) and b.item_number=v.ITEM_NUMBER) ITEM_DESC 
    FROM CMR_XLA_INTERORG_TXNS_V v
    union all
    SELECT ACCOUNTING_EVENT_ID, TRANSACTION_TYPE, TRADE_ORG_CODE PO_NUMBER, null VENDOR_ID, '' VENDOR_NAME, TRADE_ORG_CODE, 'Referencia de envío' SOURCE_DOCUMENT_TYPE, ITEM_NUMBER, 
           null PO_LINE_NUMBER, null RECEIPT_NUMBER,
          (SELECT b.description FROM EGP_SYSTEM_ITEMS b where b.ORGANIZATION_ID in (select distinct master_organization_id from INV_ORG_PARAMETERS s) and b.item_number=v.ITEM_NUMBER) ITEM_DESC 
    FROM CMR_XLA_TRADE_TXNS_V v
    union all
    SELECT ACCOUNTING_EVENT_ID, TRANSACTION_TYPE, INV_ORG_CODE PO_NUMBER, null VENDOR_ID, '' VENDOR_NAME, INV_ORG_CODE TRADE_ORG_CODE, 'Recepción de orden de transferencia' SOURCE_DOCUMENT_TYPE, ITEM_NUMBER,
          null PO_LINE_NUMBER, null RECEIPT_NUMBER,
          (SELECT b.description FROM EGP_SYSTEM_ITEMS b where b.ORGANIZATION_ID in (select distinct master_organization_id from INV_ORG_PARAMETERS s) and b.item_number=v.ITEM_NUMBER) ITEM_DESC 
    FROM CMR_XLA_TRO_TXNS_V v
  ) --select * from po_rcv
  ,
  rm_docs as (
      SELECT 
          vcch.customer_contract_number   revenue_contract
         ,vpo.perf_obligation_number      performance_obligation
         ,PERF_OBLIGATION_LINE_NUMBER
         ,hp.party_name                   customer_name
         ,hca.account_number              customer_account_number
         ,esi.item_number
         ,vsdt.name source_doc_reference
         ,vsd.SRC_ATTRIBUTE_CHAR1         nro_contrato
         ,vsd.SRC_ATTRIBUTE_CHAR7         pdc
    FROM VRM_CUSTOMER_CONTRACT_HEADERS   vcch,
         VRM_PERF_OBLIGATIONS            vpo,
         VRM_PERF_OBLIGATION_LINES       vpol,
         HZ_PARTIES                      hp,
         HZ_CUST_ACCOUNTS                hca,
         VRM_SOURCE_DOCUMENTS            vsd,
         VRM_SOURCE_DOC_LINES            vsdl,
         VRM_SOURCE_DOC_TYPES_VL         vsdt,
         EGP_SYSTEM_ITEMS_VL             esi
    WHERE vpo.customer_contract_header_id = vcch.customer_contract_header_id
     AND  vpo.perf_obligation_id = vpol.perf_obligation_id
     AND  vsd.bill_to_customer_id = hca.cust_account_id
     AND  vsd.document_id = vsdl.document_id
     AND  hca.party_id = hp.party_id
     AND  vsdl.document_line_id = vpol.document_line_id
     AND  vsdl.DOCUMENT_TYPE_ID = vsdt.DOCUMENT_TYPE_ID
     AND  vsdl.item_id = esi.inventory_item_id
     AND  vsdl.inventory_org_id = esi.inventory_organization_id
  ),
  xla_journals as (
      SELECT XTE.ENTITY_CODE, XLAH.ENTITY_ID, XLAH.EVENT_ID, XLAH.LEDGER_ID, XLAL.APPLICATION_ID, APPLICATION_NAME, XLAL.AE_HEADER_ID, XLAL.AE_LINE_NUM, GIR.JE_HEADER_ID, GIR.JE_LINE_NUM, 
             XTE.SOURCE_ID_CHAR_1, XTE.SOURCE_ID_INT_1, SOURCE_ID_INT_2, TRANSACTION_NUMBER, EVENT_TYPE_CODE, XLAL.DESCRIPTION,
             XLAL.CODE_COMBINATION_ID, XLAL.ENTERED_DR, XLAL.ENTERED_CR,  NVL(XLAL.ACCOUNTED_DR, 0) ACCOUNTED_DR, NVL(XLAL.ACCOUNTED_CR, 0) ACCOUNTED_CR, XLAL.LAST_UPDATE_DATE             
       FROM GL_IMPORT_REFERENCES GIR
           ,XLA_AE_LINES XLAL
           ,XLA_AE_HEADERS XLAH
           ,XLA_TRANSACTION_ENTITIES XTE
           ,fnd_application_vl app
       WHERE  GIR.GL_SL_LINK_TABLE = XLAL.GL_SL_LINK_TABLE
          AND GIR.GL_SL_LINK_ID = XLAL.GL_SL_LINK_ID
          AND XLAL.APPLICATION_ID = XLAH.APPLICATION_ID
          AND XLAL.APPLICATION_ID = APP.APPLICATION_ID
          AND XLAL.AE_HEADER_ID = XLAH.AE_HEADER_ID
          AND XTE.ENTITY_ID = XLAH.ENTITY_ID
          AND XLAH.GL_TRANSFER_STATUS_CODE = 'Y'
   --       and JE_HEADER_ID=59032 and JE_LINE_NUM='3'
  --and XTE.ENTITY_CODE='REVENUE' and XLAL.APPLICATION_ID = 10036
  ),
  gl_journals as (
        SELECT /*+ INLINE */
               gjl.period_name
             , gld.name
        	 , pc.PERIOD_YEAR 
        	 , pc.PERIOD_NUM 
             , decode(nvl((select 'R' from gl_je_headers h where h.REVERSED_JE_HEADER_ID=gjh.JE_HEADER_ID) || case when gjh.REVERSED_JE_HEADER_ID is not null then 'R' else '' end, gjh.STATUS), 'P', 'Contabilizado', 'U', 'Sin Contabilizar', 'R', 'Reversado') status
        	 , decode(gcc.account_type, 'A', 'Activo', 'L', 'Pasivo',  'E', 'Gasto',  'R', 'Ingreso',  'O', 'Orden') account_type
             , gjh.je_batch_id
             , gjl.JE_HEADER_ID
             , gjl.JE_LINE_NUM
             , REVERSED_JE_HEADER_ID
             , case when (select min(GL_SL_LINK_ID) from GL_IMPORT_REFERENCES r where r.JE_HEADER_ID=gjl.JE_HEADER_ID and r.JE_LINE_NUM=gjl.JE_LINE_NUM) is null 
               then 'General Ledger' else gjs.user_je_source_name end APPLICATION_NAME
        	 , gcc.segment1
        	 , gcc.segment2
        	 , gcc.segment3 
             , gcc.segment4 
             , gcc.segment5 
             , gcc.segment6 
             , gcc.segment7 
             , gcc.segment8
             , gjh.posting_acct_seq_value 
             , replace(replace(replace(gjl.description,'"','') , chr(10),'') , chr(13),'') l_description
             , replace(replace(replace(gjh.description,'"','') , chr(10),'') , chr(13),'') h_description
             , gjh.creation_date
        	 , gjl.effective_date 	 
             , gjh.posted_date	 
        	 , NVL(gjl.accounted_dr, 0) gl_accounted_dr
             , NVL(gjl.accounted_cr, 0) gl_accounted_cr
             , gjs.user_je_source_name
             , gjc.user_je_category_name
             , gjb.name batch_name 
             , gjh.name header_name 
             , gcc.CHART_OF_ACCOUNTS_ID
             , gcc.code_combination_id
             , gjl.JGZZ_RECON_REF
        FROM gl_je_batches gjb
           , gl_je_headers gjh
           , gl_je_sources_tl gjs
           , gl_je_categories_tl gjc
           , gl_je_lines gjl
           , gl_ledgers gld
           , gl_code_combinations gcc
           , gl_periods pc
        WHERE gjb.je_batch_id              = gjh.je_batch_id 
        AND   gjh.je_source                = gjs.je_source_name
        AND   gjh.je_category              = gjc.je_category_name
        AND   gjh.ledger_id                = gld.ledger_id
        AND   gjh.je_header_id             = gjl.je_header_id
        AND   gjl.code_combination_id      = gcc.code_combination_id
        AND   gld.period_set_name          = pc.period_set_name
        AND   gjl.period_name              = pc.period_name
        AND   gjs.LANGUAGE                 = 'E'
        AND   gjc.LANGUAGE                 = 'E'
        AND   gjl.currency_code           <> 'STAT'
        AND   gjh.status            = 'P' 
  ),
  xla_base as (
      select j.*, nvl(AI.PAY_GROUP_LOOKUP_CODE, ' ')||'|'||AI.INVOICE_NUM||'|Proveedor|'|| pn.PARTY_NUMBER ||'|'||pn.PARTY_NAME||'|'|| 
            nvl((SELECT CLASSIFICATION_NAME FROM ZX_FC_CODES_DENORM_VL FC WHERE FC.CLASSIFICATION_TYPE_CODE = 'DOCUMENT_SUBTYPE' AND COUNTRY_CODE='CL' and fc.CLASSIFICATION_CODE=AI.DOCUMENT_SUB_TYPE) , AI.INVOICE_TYPE_LOOKUP_CODE)|| ' / ' || (select min(ph.segment1) from ap_invoice_distributions_all aid, po_headers_all ph, po_distributions_all pld 
               where ph.po_header_id=pld.po_header_id and aid.PO_DISTRIBUTION_ID=pld.PO_DISTRIBUTION_ID and aid.INVOICE_ID=ai.INVOICE_ID) ds  
      from xla_journals j, AP_INVOICES_ALL AI, party_num pn
      where AI.PARTY_ID = pn.PARTY_ID 
          AND AI.INVOICE_ID = j.SOURCE_ID_INT_1
          AND j.ENTITY_CODE = 'AP_INVOICES'
          AND j.APPLICATION_ID = 200
      union all
      select j.*, 'Pago|'||AI.CHECK_NUMBER||'|Proveedor|'|| pn.PARTY_NUMBER ||'|'||pn.PARTY_NAME ds 
      from xla_journals j, ap_checks_all AI, party_num pn
      where AI.PARTY_ID = pn.PARTY_ID 
          AND AI.CHECK_ID = j.SOURCE_ID_INT_1
          AND j.ENTITY_CODE = 'AP_PAYMENTS'
          AND j.APPLICATION_ID = 200
      union all
      select j.*, regexp_substr(TP.NAME, '[^_]+', 1,1)||'|'||AI.TRX_NUMBER||'|Cliente|'||nvl(PS.ACCOUNT_NUMBER,'000')||'|'||PI.PARTY_NAME ds 
      from xla_journals j, ra_customer_trx_all AI, hz_cust_accounts PS, hz_parties PI, RA_CUST_TRX_TYPES_ALL TP
      where AI.BILL_TO_CUSTOMER_ID = PS.CUST_ACCOUNT_ID 
        AND PS.PARTY_ID = PI.PARTY_ID 
        AND AI.CUST_TRX_TYPE_SEQ_ID = TP.CUST_TRX_TYPE_SEQ_ID
        AND AI.CUSTOMER_TRX_ID = j.SOURCE_ID_INT_1
        AND j.ENTITY_CODE = 'TRANSACTIONS'
        AND j.APPLICATION_ID = 222
      union all
      select j.*, 'Cobro|'||AI.RECEIPT_NUMBER||'|Cliente|'||nvl(PS.ACCOUNT_NUMBER,'000')||'|'||PI.PARTY_NAME ds 
      from xla_journals j, ar_cash_receipts_all AI, hz_cust_accounts PS, hz_parties PI
      where AI.pay_from_customer = PS.CUST_ACCOUNT_ID(+) 
        AND PS.PARTY_ID = PI.PARTY_ID(+) 
        AND AI.CASH_RECEIPT_ID = j.SOURCE_ID_INT_1
        AND j.ENTITY_CODE = 'RECEIPTS'
        AND j.APPLICATION_ID = 222
      union all
      select j.*, 'Ajuste|'||ADJUSTMENT_NUMBER||'|Cliente|'||nvl(PS.ACCOUNT_NUMBER,'000')||'|'||PI.PARTY_NAME ds
      from xla_journals j, ar_adjustments_all AD, ra_customer_trx_all AI, hz_cust_accounts PS, hz_parties PI
      where AI.BILL_TO_CUSTOMER_ID = PS.CUST_ACCOUNT_ID
        AND AD.CUSTOMER_TRX_ID = AI.CUSTOMER_TRX_ID
        AND PS.PARTY_ID = PI.PARTY_ID
        AND AD.ADJUSTMENT_ID = j.SOURCE_ID_INT_1
        AND j.ENTITY_CODE = 'ADJUSTMENTS'
        AND j.APPLICATION_ID = 222
      union all
      select j.*, SOURCE_DOCUMENT_TYPE||'|'||nvl(po_number,' ')||'|Proveedor|'||nvl(INCOME_TAX_ID, ITEM_NUMBER)||'|'||nvl(VENDOR_NAME, ITEM_DESC)||'|'||po_number||' / linea: '||po_line_number||' / recep: '|| receipt_number ds
      from xla_journals j, po_rcv rt
      where rt.ACCOUNTING_EVENT_ID = j.SOURCE_ID_INT_1
        AND j.ENTITY_CODE in ('RECEIVING', 'DELIVER', 'INTER_ORG_RECEIPT', 'TRADE_ACCRUALS', 'TRO_RECEIPTS', 'PERIOD_END_ACCRUAL')
        AND j.APPLICATION_ID = 10096
      union all
      select j.*, '$USER_JE_CATEGORY_NAME$|'||TRANSACTION_HEADER_ID||'|Activo Fijo|' ||af.ASSET_NUMBER||'|'||af.DESCRIPTION||'|'||af.ATTRIBUTE_CATEGORY_CODE ds
      from xla_journals j, fa_transaction_headers fh, fa_additions_vl af
      where fh.TRANSACTION_HEADER_ID = j.SOURCE_ID_INT_1
        AND fh.BOOK_TYPE_CODE = j.SOURCE_ID_CHAR_1
        AND fh.asset_id=af.asset_id
        AND j.ENTITY_CODE = 'TRANSACTIONS'
        AND j.APPLICATION_ID = 140
      union all
      select j.*, '$USER_JE_CATEGORY_NAME$|'||j.SOURCE_ID_INT_1|| '|Activo Fijo|' ||af.ASSET_NUMBER||'|'||af.DESCRIPTION||'|'||af.ATTRIBUTE_CATEGORY_CODE ds
      from xla_journals j, fa_additions_vl af, fa_deprn_periods pd
      where af.asset_id = j.SOURCE_ID_INT_1
        AND pd.BOOK_TYPE_CODE = j.SOURCE_ID_CHAR_1
        AND pd.PERIOD_COUNTER = j.SOURCE_ID_INT_2
        AND j.ENTITY_CODE = 'DEPRECIATION'
        AND j.APPLICATION_ID = 140
      union all
      select j.*, TRANSACTION_TYPE_NAME||'|'|| TRANSACTION_NUMBER || '|Artículo|'|| ITEM_NUMBER || '|'|| inv.description || '|'|| inv.ORGANIZATION_NAME ||' - '|| inv.SUBINVENTORY_CODE ds
      from xla_journals j, CST_XLA_DISTRIBUTIONS_V pd, inventory inv
      where pd.distribution_id = j.SOURCE_ID_INT_1 
        AND j.AE_HEADER_ID=inv.AE_HEADER_ID 
        AND j.AE_LINE_NUM=inv.AE_LINE_NUM 
        AND j.APPLICATION_ID = 707
      union all
      select j.*, DOCUMENT_NAME||'|'||EXPENDITURE_ITEM_ID||'|Tipo de Gasto|'||EXPENDITURE_TYPE||'|'||ORIG_TRANSACTION_REFERENCE||'|Proyecto '||project_number ds
      from xla_journals j, pjc_cost pd
      where pd.expenditure_item_id = j.SOURCE_ID_INT_1  
        and j.ENTITY_CODE='EXPENDITURES'
      union all
      select j.*, 'Contrato|'||pd.CONTRACT_NUMBER||'|Cliente|'||c.ACCOUNT_NUMBER||'|'||hp.PARTY_NAME||'|'||REVENUE_CATEGORY ds
      from xla_journals j, PJB_XLA_REV_V pd, OKC_K_HEADERS_ALL_B k, HZ_CUST_ACCOUNTS c, hz_parties hp, PJB_XLA_EVENTS_REF_V e 
      where j.SOURCE_ID_INT_1=pd.REV_DISTRIBUTION_ID(+)
        and j.ENTITY_CODE='REVENUE'
        and j.APPLICATION_ID = 10036
        and pd.CONTRACT_ID=k.ID
        and pd.BILLING_EVENT_ID=e.BILLING_EVENT_ID
        and k.VERSION_TYPE in ('C') 
        and k.BILL_TO_ACCT_ID=c.CUST_ACCOUNT_ID(+)
        and c.party_id =hp.party_id(+)
      union all
      select j.*, r.SOURCE_DOC_REFERENCE||'|'||r.nro_contrato||'|Cliente|'||r.CUSTOMER_ACCOUNT_NUMBER||'|'||r.CUSTOMER_NAME||'|'||r.ITEM_NUMBER||' / '|| nro_contrato ||' / '|| r.pdc ds
      from xla_journals j, rm_docs r
      where j.SOURCE_ID_INT_1=r.REVENUE_CONTRACT
        and j.APPLICATION_ID = 10455 
        and j.ENTITY_CODE = 'REV_CONTRACT'
        and regexp_substr(regexp_substr(j.DESCRIPTION, '[^:]+', 1,6), '[^ ]+', 1,1)=r.PERFORMANCE_OBLIGATION||''
        and regexp_substr(regexp_substr(j.DESCRIPTION, '[^:]+', 1,7), '[^ ]+', 1,1)=r.PERF_OBLIGATION_LINE_NUMBER||''
 )
  -- Aquí solo contamos, por performance usamos SELECT 1
  SELECT 1
  FROM gl_journals j
  LEFT JOIN xla_base x
    ON  j.je_header_id = x.je_header_id
   AND j.JE_LINE_NUM  = x.JE_LINE_NUM
  JOIN coscen c
    ON  j.segment4 = c.flex_value
  LEFT JOIN gestion_assig g
    ON  c.FormaCons = g.forma
   AND j.segment3  = g.cuenta
  WHERE j.account_type IN ('Gasto','Ingreso') and NVL(x.APPLICATION_NAME, j.APPLICATION_NAME) <> 'General Ledger'
)

-- Consulta registrada el 2026-02-06T01:15:43.197985
select * from dual

-- Consulta registrada el 2025-09-29T17:26:14.544689
SELECT 
        RTRIM(LTRIM(ROW_ID)) AS ROW_ID,
        RTRIM(LTRIM(LOOKUP_TYPE)) AS LOOKUP_TYPE,
        RTRIM(LTRIM(LOOKUP_CODE)) AS LOOKUP_CODE,
        RTRIM(LTRIM(MEANING)) AS MEANING,
        RTRIM(LTRIM(DESCRIPTION)) AS DESCRIPTION,
        ENABLED_FLAG,
        START_DATE_ACTIVE,
        END_DATE_ACTIVE,
        RTRIM(LTRIM(CREATED_BY)) AS CREATED_BY,
        TO_CHAR(CREATION_DATE, 'YYYY-MM-DD HH24:MI:SS') AS CREATION_DATE,
        RTRIM(LTRIM(LAST_UPDATED_BY)) AS LAST_UPDATED_BY,
        TO_CHAR(LAST_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS LAST_UPDATE_DATE,
        RTRIM(LTRIM(ATTRIBUTE1)) AS ATTRIBUTE1, -- full (y/n) 
        RTRIM(LTRIM(ATTRIBUTE2)) AS ATTRIBUTE2, -- area
        RTRIM(LTRIM(ATTRIBUTE3)) AS ATTRIBUTE3, -- sub area
        RTRIM(LTRIM(ATTRIBUTE4)) AS ATTRIBUTE4, -- centro de costo
        RTRIM(LTRIM(ATTRIBUTE5)) AS ATTRIBUTE5  -- cuenta gestion
    FROM fnd_lookup_values_vl
    WHERE LOOKUP_TYPE = 'UAI_SEGURIDAD_REPORTES'
      AND ENABLED_FLAG = 'Y'

-- Consulta registrada el 2025-09-29T15:52:24.195400
SELECT 
  row_id,
  flex_value,
  flex_value_id,
  enabled_flag,
  start_date_active,
  end_date_active,
  value_category,
  attribute1, -- atributo de Centro de Costo: es centro ej: el ces, cefis
  attribute2, -- atributo de Centro de Costo: tipo de area ej: academica o administrativa
  attribute3, -- atributo de Centro de Costo: area relacionado con la linea area
  attribute4, -- atributo de Centro de Costo: sub area
  attribute5,  -- atributo de Centro de Costo: tipo programa
  attribute6,  -- atributo de Centro de Costo: subtipo porgrama 
  attribute7,  -- atributo de Centro de Costo: sede
  attribute8,  -- atributo de Centro de Costo: con auxliar
  attribute9,  -- atributo de Centro de Costo: nota ifrs
  attribute10,  -- atributo de Centro de Costo: nota ses
  attribute11,  -- atributo de Centro de Costo: sub grupo ses
  attribute12,  -- atributo de Centro de Costo: forma consolidado gestion 
  attribute13,  -- atributo de Centro de Costo: forma consolidado ifrs 
  attribute14,  -- atributo de Centro de Costo: forma consolidado ses 
  last_update_date,
  description,
  creation_date
FROM FND_FLEX_VALUES_VL
WHERE value_category IN (
  'Cuenta de Gestion UAI'    -- tabla independiente
) and enabled_flag = 'Y' and flex_value_id = '89077'

-- Consulta registrada el 2025-09-29T15:51:12.365053
SELECT 
  row_id,
  flex_value,
  flex_value_id,
  enabled_flag,
  start_date_active,
  end_date_active,
  value_category,
  attribute1, -- atributo de Centro de Costo: es centro ej: el ces, cefis
  attribute2, -- atributo de Centro de Costo: tipo de area ej: academica o administrativa
  attribute3, -- atributo de Centro de Costo: area relacionado con la linea area
  attribute4, -- atributo de Centro de Costo: sub area
  attribute5,  -- atributo de Centro de Costo: tipo programa
  attribute6,  -- atributo de Centro de Costo: subtipo porgrama 
  attribute7,  -- atributo de Centro de Costo: sede
  attribute8,  -- atributo de Centro de Costo: con auxliar
  attribute9,  -- atributo de Centro de Costo: nota ifrs
  attribute10,  -- atributo de Centro de Costo: nota ses
  attribute11,  -- atributo de Centro de Costo: sub grupo ses
  attribute12,  -- atributo de Centro de Costo: forma consolidado gestion 
  attribute13,  -- atributo de Centro de Costo: forma consolidado ifrs 
  attribute14,  -- atributo de Centro de Costo: forma consolidado ses 
  last_update_date,
  description,
  creation_date
FROM FND_FLEX_VALUES_VL
WHERE value_category IN (
  'Cuenta de Gestion UAI'    -- tabla independiente
) and enabled_flag = 'Y' and flex_value_id = '89059'

-- Consulta registrada el 2025-09-29T15:50:40.172740
SELECT 
  row_id,
  flex_value,
  flex_value_id,
  enabled_flag,
  start_date_active,
  end_date_active,
  value_category,
  attribute1, -- atributo de Centro de Costo: es centro ej: el ces, cefis
  attribute2, -- atributo de Centro de Costo: tipo de area ej: academica o administrativa
  attribute3, -- atributo de Centro de Costo: area relacionado con la linea area
  attribute4, -- atributo de Centro de Costo: sub area
  attribute5,  -- atributo de Centro de Costo: tipo programa
  attribute6,  -- atributo de Centro de Costo: subtipo porgrama 
  attribute7,  -- atributo de Centro de Costo: sede
  attribute8,  -- atributo de Centro de Costo: con auxliar
  attribute9,  -- atributo de Centro de Costo: nota ifrs
  attribute10,  -- atributo de Centro de Costo: nota ses
  attribute11,  -- atributo de Centro de Costo: sub grupo ses
  attribute12,  -- atributo de Centro de Costo: forma consolidado gestion 
  attribute13,  -- atributo de Centro de Costo: forma consolidado ifrs 
  attribute14,  -- atributo de Centro de Costo: forma consolidado ses 
  last_update_date,
  description,
  creation_date
FROM FND_FLEX_VALUES_VL
WHERE value_category IN (
  'Cuenta de Gestion UAI'    -- tabla independiente
) and enabled_flag = 'Y' and flex_value_id = '89056'

-- Consulta registrada el 2025-09-29T15:49:10.780628
SELECT 
  row_id,
  flex_value,
  flex_value_id,
  enabled_flag,
  start_date_active,
  end_date_active,
  value_category,
  attribute1, -- atributo de Centro de Costo: es centro ej: el ces, cefis
  attribute2, -- atributo de Centro de Costo: tipo de area ej: academica o administrativa
  attribute3, -- atributo de Centro de Costo: area relacionado con la linea area
  attribute4, -- atributo de Centro de Costo: sub area
  attribute5,  -- atributo de Centro de Costo: tipo programa
  attribute6,  -- atributo de Centro de Costo: subtipo porgrama 
  attribute7,  -- atributo de Centro de Costo: sede
  attribute8,  -- atributo de Centro de Costo: con auxliar
  attribute9,  -- atributo de Centro de Costo: nota ifrs
  attribute10,  -- atributo de Centro de Costo: nota ses
  attribute11,  -- atributo de Centro de Costo: sub grupo ses
  attribute12,  -- atributo de Centro de Costo: forma consolidado gestion 
  attribute13,  -- atributo de Centro de Costo: forma consolidado ifrs 
  attribute14,  -- atributo de Centro de Costo: forma consolidado ses 
  last_update_date,
  description,
  creation_date
FROM FND_FLEX_VALUES_VL
WHERE value_category IN (
  'Cuenta de Gestion UAI'    -- tabla independiente
) and enabled_flag = 'Y'

-- Consulta registrada el 2025-09-29T15:17:17.188186
select *
from FND_LOOKUP_VALUES_VL 
where LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'

-- Consulta registrada el 2025-09-29T15:16:05.178964
select *
from FND_LOOKUP_VALUES_VL 
where LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'

-- Consulta registrada el 2025-09-29T15:15:38.421088
select 
    concat ('GEST-', LOOKUP_CODE) as  ID_CONS, 
    LOOKUP_CODE, 
    MEANING, 
    ENABLED_FLAG, 
     case 
        when ATTRIBUTE1 = '' then ''
        when ATTRIBUTE1 <> '' then concat ('GEST-', ATTRIBUTE1)
        else ''
    end as ID_PADRE, 
    DISPLAY_SEQUENCE, 
    LOOKUP_TYPE, 
    LAST_UPDATE_DATE 
from FND_LOOKUP_VALUES_VL 
where LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'

-- Consulta registrada el 2025-09-29T15:13:04.182741
select 
    concat ('GEST-', LOOKUP_CODE) as  ID_CONS, 
    LOOKUP_CODE, 
    MEANING, 
    ENABLED_FLAG, 
     case 
        when ATTRIBUTE1 = '' then ''
        when ATTRIBUTE1 <> '' then concat ('GEST-', ATTRIBUTE1)
        else ''
    end as ID_PADRE, 
    DISPLAY_SEQUENCE, 
    LOOKUP_TYPE, 
    LAST_UPDATE_DATE 
from FND_LOOKUP_VALUES_VL 
where LOOKUP_TYPE = 'UAI_CONS_GESTION_FORMAS'

-- Consulta registrada el 2025-09-29T15:11:36.030681
select 
    concat ('GEST-', LOOKUP_CODE) as  ID_CONS, 
    LOOKUP_CODE, 
    MEANING, 
    ENABLED_FLAG, 
     case 
        when ATTRIBUTE1 = '' then ''
        when ATTRIBUTE1 <> '' then concat ('GEST-', ATTRIBUTE1)
        else ''
    end as ID_PADRE, 
    DISPLAY_SEQUENCE, 
    LOOKUP_TYPE, 
    LAST_UPDATE_DATE 
from FND_LOOKUP_VALUES_VL 
where LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'

-- Consulta registrada el 2025-09-29T15:02:02.372775
select 
    concat ('GEST-', LOOKUP_CODE) as  ID_CONS, 
    LOOKUP_CODE, 
    MEANING, 
    ENABLED_FLAG, 
     case 
        when ATTRIBUTE1 = '' then ''
        when ATTRIBUTE1 <> '' then concat ('GEST-', ATTRIBUTE1)
        else ''
    end as ID_PADRE, 
    DISPLAY_SEQUENCE, 
    LOOKUP_TYPE, 
    LAST_UPDATE_DATE 
from FND_LOOKUP_VALUES_VL 
where LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'

-- Consulta registrada el 2025-09-29T15:00:55.865971
select 
    concat ('SIES-', LOOKUP_CODE) as  ID_CONS, 
    LOOKUP_CODE, 
    MEANING, 
    ENABLED_FLAG, 
    case 
        when ATTRIBUTE1 = '' then ''
        when ATTRIBUTE1 <> '' then concat ('SIES-', ATTRIBUTE1)
        else ''
    end as ID_PADRE, 
    DISPLAY_SEQUENCE, 
    LOOKUP_TYPE, 
    LAST_UPDATE_DATE 
from FND_LOOKUP_VALUES_VL 
where LOOKUP_TYPE = 'UAI_CONS_SIES_ESTRUCTURA'

-- Consulta registrada el 2025-09-29T14:58:17.239910
select 
    concat ('GEST-', LOOKUP_CODE) as  ID_CONS, 
    LOOKUP_CODE, 
    MEANING, 
    ENABLED_FLAG, 
     case 
        when ATTRIBUTE1 = '' then ''
        when ATTRIBUTE1 <> '' then concat ('GEST-', ATTRIBUTE1)
        else ''
    end as ID_PADRE, 
    DISPLAY_SEQUENCE, 
    LOOKUP_TYPE, 
    LAST_UPDATE_DATE 
from FND_LOOKUP_VALUES_VL 
where LOOKUP_TYPE = 'UAI_CONS_GESTION_ESTRUCTURA'