APPS.ZX_PO_REC_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 27

C_LINES_PER_INSERT                 CONSTANT NUMBER :=  5000;

Line: 36

SELECT /*+ ORDERED NO_EXPAND use_nl(pod, pol, poll, atc,atg, atc1, pov) */
       NVL(poll.po_release_id, poh.po_header_id),
       pod.line_location_id,
       pod.po_distribution_id,
       NVL(atg.tax_code_id,atc.tax_id),
       poh.last_update_date,
       pod.code_combination_id,
       poh.vendor_id,
       pod.tax_recovery_override_flag,
       pod.recovery_rate,
       poh.vendor_site_id,
       pol.item_id,
       poh.inventory_organization_id,
       poh.chart_of_accounts_id,
       atc1.tax_recovery_rule_id,
       atc1.tax_recovery_rate,
       pov.vendor_type_lookup_code
 FROM
      ( SELECT /*+ ROWID(poh) NO_MERGE swap_join_inputs(fsp) swap_join_inputs(lgr)
                   INDEX (fsp financials_system_params_u1) INDEX (lgr gl_ledgers_u2)*/
               poh.po_header_id, poh.last_update_date,poh.vendor_id,poh.vendor_site_id,
               fsp.set_of_books_id, fsp.org_id, fsp.inventory_organization_id,
               lgr.chart_of_accounts_id
          FROM po_headers_all poh,
      	       financials_system_params_all fsp,
      	       xla_upgrade_dates upd,
      	       gl_ledgers lgr
         WHERE poh.rowid BETWEEN p_start_rowid AND p_end_rowid
           AND NVL(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED'
           AND NVL(poh.org_id, -99) = NVL(fsp.org_id, -99)
           AND upd.ledger_id = fsp.set_of_books_id
           AND (NVL(poh.closed_code, 'OPEN') = 'OPEN' OR
                trunc(poh.last_update_date) >= upd.start_date
               )
           AND lgr.ledger_id = fsp.set_of_books_id
      ) poh,
      po_distributions_all pod,
      po_line_locations_all poll,
      po_lines_all pol,
      ap_tax_codes_all atc,
      ar_tax_group_codes_all atg,
      ap_tax_codes_all atc1,
      po_vendors pov
WHERE poh.po_header_id = pod.po_header_id
  AND pol.po_header_id = poll.po_header_id
  AND pol.po_line_id = poll.po_line_id
  AND poll.po_header_id = pod.po_header_id
  AND poll.po_line_id = pod.po_line_id
  AND poll.line_location_id = pod.line_location_id
  AND nvl(atc.org_id,-99)=nvl(poh.org_id,-99)
  AND poll.tax_code_id = atc.tax_id(+)
  AND poll.tax_code_id = atg.tax_group_id(+)
  --Bug 8352135
  AND atg.start_date <= poll.last_update_date
  AND (atg.end_date >= poll.last_update_date OR atg.end_date IS NULL)
  AND atg.ENABLED_FLAG='Y'
  AND atc.tax_type = 'TAX_GROUP'
  AND pod.recovery_rate IS NULL
  AND atc1.tax_id(+) = atg.tax_code_id
  AND atc1.enabled_flag(+) = 'Y'
  AND pov.vendor_id = poh.vendor_id;

Line: 122

    LIMIT C_LINES_PER_INSERT;

Line: 151

            SELECT TAX_RATE_ID INTO l_tax_rate_id
            FROM ZX_RATES_B
            WHERE NVL(source_id,tax_rate_id) = pg_tax_code_id_tab(i)
	    AND record_type_code='MIGRATED';

Line: 166

      insert_rec_info;

Line: 194

PROCEDURE INSERT_REC_INFO
IS

  l_count       number;

Line: 208

    INSERT INTO  ZX_PO_REC_DIST
    ( PO_HEADER_ID,
      PO_LINE_LOCATION_ID,
      PO_DISTRIBUTION_ID,
      REC_RATE,
      TAX_RATE_ID,
      CREATED_BY ,
      CREATION_DATE ,
      LAST_UPDATED_BY ,
      LAST_UPDATE_DATE ,
      LAST_UPDATE_LOGIN
    )
    VALUES
    (
    PG_PO_HEADER_ID_TAB(i),
    PG_PO_LINE_ID_TAB(i),
    PG_PO_DIST_ID_TAB(i),
    pg_get_tax_recovery_rate_tab(i),
    PG_TAX_CODE_ID_TAB(i),
    1,
    SYSDATE,
    1,
    SYSDATE,
    1   );

Line: 237

  END INSERT_REC_INFO;

Line: 246

 PG_TAX_CODE_ID_TAB.DELETE;

Line: 247

 PG_TRX_DATE_TAB.DELETE;

Line: 248

 PG_CODE_COMBINATION_ID_TAB.DELETE;

Line: 249

 PG_VENDOR_ID_TAB.DELETE;

Line: 250

 PG_TAX_REC_OVERRIDE_FLAG_TAB.DELETE;

Line: 251

 PG_TAX_RECOVERY_RATE_TAB.DELETE;

Line: 252

 PG_VENDOR_SITE_ID_TAB.DELETE;

Line: 253

 PG_ITEM_ID_TAB.DELETE;

Line: 254

 PG_INV_ORG_ID_TAB.DELETE;

Line: 264

 SELECT /*+ ORDERED NO_EXPAND use_nl(pod, poll,pol, atc,atg, atc1, pov)
            INDEX (fsp financials_system_params_u1) INDEX (lgr gl_ledgers_u2)*/
        poh.po_header_id,
        pod.line_location_id,
        pod.po_distribution_id,
        NVL(atg.tax_code_id, atc.tax_id),
        poh.last_update_date,
        pod.code_combination_id,
        poh.vendor_id,
        pod.tax_recovery_override_flag,
        pod.recovery_rate,
        poh.vendor_site_id,
        pol.item_id,
        fsp.inventory_organization_id,
        lgr.chart_of_accounts_id,
        atc1.tax_recovery_rule_id,
        atc1.tax_recovery_rate,
        pov.vendor_type_lookup_code
   FROM po_headers_all poh,
        po_distributions_all pod,
        po_line_locations_all poll,
        po_lines_all pol,
        ap_tax_codes_all atc,
        ar_tax_group_codes_all atg,
        ap_tax_codes_all atc1,
        po_vendors pov,
        financials_system_params_all fsp,
        gl_ledgers lgr
  WHERE poh.po_header_id = p_upg_trx_info_rec.trx_id
    AND pod.po_header_id = poh.po_header_id
    AND pod.recovery_rate IS NULL
    AND poll.po_header_id = pod.po_header_id
    AND poll.po_line_id = pod.po_line_id
    AND poll.line_location_id = pod.line_location_id
    AND pol.po_header_id = poll.po_header_id
    AND pol.po_line_id = poll.po_line_id
    AND nvl(atc.org_id,-99)=nvl(poh.org_id,-99)
    AND poll.tax_code_id = atc.tax_id
    AND poll.tax_code_id = atg.tax_group_id
    AND atc.tax_type = 'TAX_GROUP'
    AND atc1.tax_id = atg.tax_code_id
    AND atc1.enabled_flag = 'Y'
    AND pov.vendor_id = poh.vendor_id
    AND NVL(fsp.org_id, -99) = NVL(poh.org_id, -99)
    AND lgr.ledger_id = fsp.set_of_books_id;

Line: 311

 SELECT /*+ ORDERED NO_EXPAND use_nl(pod, poh, pol, atc,atg, atc1, pov)
            INDEX (fsp financials_system_params_u1) INDEX (lgr gl_ledgers_u2)*/
        poll.po_release_id,
        pod.line_location_id,
        pod.po_distribution_id,
        NVL(atg.tax_code_id, atc.tax_id),
        poh.last_update_date,
        pod.code_combination_id,
        poh.vendor_id,
        pod.tax_recovery_override_flag,
        pod.recovery_rate,
        poh.vendor_site_id,
        pol.item_id,
        fsp.inventory_organization_id,
        lgr.chart_of_accounts_id,
        atc1.tax_recovery_rule_id,
        atc1.tax_recovery_rate,
        pov.vendor_type_lookup_code
  FROM po_line_locations_all poll,
       po_distributions_all pod,
       po_headers_all poh,
       po_lines_all pol,
       ap_tax_codes_all atc,
       ar_tax_group_codes_all atg,
       ap_tax_codes_all atc1,
       po_vendors pov,
       financials_system_params_all fsp,
       gl_ledgers lgr
 WHERE poll.po_release_id = p_upg_trx_info_rec.trx_id
   AND pod.po_header_id = poll.po_header_id
   AND pod.po_line_id = poll.po_line_id
   AND pod.line_location_id = poll.line_location_id
   AND pod.recovery_rate IS NULL
   AND poh.po_header_id = poll.po_header_id
   AND pol.po_header_id = poll.po_header_id
   AND pol.po_line_id = poll.po_line_id
   AND nvl(atc.org_id,-99)=nvl(poh.org_id,-99)
   AND poll.tax_code_id = atc.tax_id
   AND poll.tax_code_id = atg.tax_group_id
   AND atc.tax_type = 'TAX_GROUP'
   AND atc1.tax_id = atg.tax_code_id
   AND atc1.enabled_flag = 'Y'
   AND pov.vendor_id = poh.vendor_id
   AND NVL(fsp.org_id, -99) = NVL(poh.org_id, -99)
   AND lgr.ledger_id = fsp.set_of_books_id;

Line: 382

      LIMIT C_LINES_PER_INSERT;

Line: 411

            SELECT TAX_RATE_ID INTO l_tax_rate_id
            FROM ZX_RATES_B
            WHERE NVL(source_id,tax_rate_id) = pg_tax_code_id_tab(i)
            AND record_type_code='MIGRATED';

Line: 426

        insert_rec_info;

Line: 459

      LIMIT C_LINES_PER_INSERT;

Line: 489

            SELECT TAX_RATE_ID INTO l_tax_rate_id
            FROM ZX_RATES_B
            WHERE NVL(source_id,tax_rate_id) = pg_tax_code_id_tab(i)
            AND record_type_code='MIGRATED';

Line: 503

        insert_rec_info;

Line: 534

 SELECT /*+ ORDERED NO_EXPAND use_nl(pod,poll,pol, atc,atg, atc1, pov)
            INDEX (fsp financials_system_params_u1) INDEX (lgr gl_ledgers_u2)*/
       poh.po_header_id,
       pod.line_location_id,
       pod.po_distribution_id,
       NVL(atg.tax_code_id,atc.tax_id),
       poh.last_update_date,
       pod.code_combination_id,
       poh.vendor_id,
       pod.tax_recovery_override_flag,
       pod.recovery_rate,
       poh.vendor_site_id,
       pol.item_id,
       fsp.inventory_organization_id,
       lgr.chart_of_accounts_id,
       atc1.tax_recovery_rule_id,
       atc1.tax_recovery_rate,
       pov.vendor_type_lookup_code
  FROM (select distinct other_doc_application_id, other_doc_trx_id
          from ZX_VALIDATION_ERRORS_GT
         where other_doc_application_id = 201
           and other_doc_entity_code = 'PURCHASE_ORDER'
           and other_doc_event_class_code = 'PO_PA'
       ) zxvalerr,
       po_headers_all poh,
       po_distributions_all pod,
       po_line_locations_all poll,
       po_lines_all pol,
       ap_tax_codes_all atc,
       ar_tax_group_codes_all atg,
       ap_tax_codes_all atc1,
       po_vendors pov,
       financials_system_params_all fsp,
       gl_ledgers lgr
 WHERE poh.po_header_id = zxvalerr.other_doc_trx_id
   AND pod.po_header_id = poh.po_header_id
   AND pod.recovery_rate IS NULL
   AND poll.po_header_id = pod.po_header_id
   AND poll.po_line_id = pod.po_line_id
   AND poll.line_location_id = pod.line_location_id
   AND pol.po_header_id = poll.po_header_id
   AND pol.po_line_id = poll.po_line_id
   AND nvl(atc.org_id,-99)=nvl(poh.org_id,-99)
   AND poll.tax_code_id = atc.tax_id
   AND poll.tax_code_id = atg.tax_group_id
   AND atc.tax_type = 'TAX_GROUP'
   AND atc1.tax_id = atg.tax_code_id
   AND atc1.enabled_flag = 'Y'
   AND pov.vendor_id = poh.vendor_id
   AND NVL(fsp.org_id, -99) = NVL(poh.org_id, -99)
   AND lgr.ledger_id = fsp.set_of_books_id
UNION
 SELECT /*+ ORDERED NO_EXPAND use_nl(pod, poh, pol, atc,atg, atc1, pov)
            INDEX (fsp financials_system_params_u1) INDEX (lgr gl_ledgers_u2)*/
       poll.po_release_id,
       pod.line_location_id,
       pod.po_distribution_id,
       NVL(atg.tax_code_id,atc.tax_id),
       poh.last_update_date,
       pod.code_combination_id,
       poh.vendor_id,
       pod.tax_recovery_override_flag,
       pod.recovery_rate,
       poh.vendor_site_id,
       pol.item_id,
       fsp.inventory_organization_id,
       lgr.chart_of_accounts_id,
       atc1.tax_recovery_rule_id,
       atc1.tax_recovery_rate,
       pov.vendor_type_lookup_code
  FROM (select distinct other_doc_application_id, other_doc_trx_id
          from ZX_VALIDATION_ERRORS_GT
       	 where other_doc_application_id = 201
       	   and other_doc_entity_code = 'RELEASE'
           and other_doc_event_class_code = 'RELEASE'
       ) zxvalerr,
       po_line_locations_all poll,
       po_headers_all poh,
       po_distributions_all pod,
       po_lines_all pol,
       ap_tax_codes_all atc,
       ar_tax_group_codes_all atg,
       ap_tax_codes_all atc1,
       po_vendors pov,
       financials_system_params_all fsp,
       gl_ledgers lgr
 WHERE poll.po_release_id = zxvalerr.other_doc_trx_id
   AND pod.po_header_id = poll.po_header_id
   AND pod.po_line_id = poll.po_line_id
   AND pod.line_location_id = poll.line_location_id
   AND pod.recovery_rate IS NULL
   AND poh.po_header_id = poll.po_header_id
   AND pol.po_header_id = poll.po_header_id
   AND pol.po_line_id = poll.po_line_id
   AND nvl(atc.org_id,-99)=nvl(poh.org_id,-99)
   AND poll.tax_code_id = atc.tax_id
   AND poll.tax_code_id = atg.tax_group_id
   AND atc.tax_type = 'TAX_GROUP'
   AND atc1.tax_id = atg.tax_code_id
   AND atc1.enabled_flag = 'Y'
   AND pov.vendor_id = poh.vendor_id
   AND NVL(fsp.org_id, -99) = NVL(poh.org_id, -99)
   AND lgr.ledger_id = fsp.set_of_books_id;

Line: 664

    LIMIT C_LINES_PER_INSERT;

Line: 693

            SELECT TAX_RATE_ID INTO l_tax_rate_id
            FROM ZX_RATES_B
            WHERE NVL(source_id,tax_rate_id) = pg_tax_code_id_tab(i)
	    AND record_type_code='MIGRATED';

Line: 708

      insert_rec_info;