DBA Data[Home] [Help]

APPS.PO_OTM_INTEGRATION_PVT SQL Statements

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

Line: 231

PROCEDURE handle_doc_update (
  p_doc_type         IN            VARCHAR2
, p_doc_id           IN            NUMBER
, p_action           IN            VARCHAR2
, p_line_id          IN            NUMBER
, p_line_loc_id      IN            NUMBER
)
IS

l_param_list       PO_EVENT_PARAMS_TYPE;
Line: 255

d_module           CONSTANT VARCHAR2(100) := g_module_prefix || 'HANDLE_DOC_UPDATE';
Line: 295

    SELECT pol.order_type_lookup_code
    INTO   l_line_value_basis
    FROM   po_lines_all pol
    WHERE  pol.po_line_id = p_line_id;
Line: 301

    SELECT pol.order_type_lookup_code
    INTO   l_line_value_basis
    FROM   po_lines_all pol,
           po_line_locations_all pll
    WHERE  pll.line_location_id = p_line_loc_id
      AND  pll.po_line_id       = pol.po_line_id;
Line: 359

      SELECT por.shipping_control
           , por.approved_date
      INTO   l_shipping_control
           , l_approved_date               --7449918
      FROM   po_releases_all por
      WHERE  por.po_release_id = p_doc_id;
Line: 370

      SELECT poh.shipping_control
           , poh.approved_date
      INTO   l_shipping_control
           , l_approved_date              --7449918
      FROM   po_headers_all poh
      WHERE  poh.po_header_id = p_doc_id;
Line: 400

        SELECT poha.revision_num
             , poha.segment1
             , pora.release_num
             , pora.revision_num
             , hou.name
        INTO   l_blanket_revision
             , l_po_number
             , l_release_number
             , l_doc_revision
             , l_org_name
        FROM   po_headers_archive_all poha
             , po_releases_archive_all pora
             , hr_all_organization_units hou
        WHERE  pora.po_release_id        = p_doc_id
          AND  pora.latest_external_flag = 'Y'
          AND  poha.po_header_id         = pora.po_header_id
          AND  poha.latest_external_flag = 'Y'
          AND  hou.organization_id       = pora.org_id;
Line: 421

        SELECT poha.segment1
             , poha.revision_num
             , hou.name
        INTO   l_po_number
             , l_doc_revision
             , l_org_name
        FROM   po_headers_archive_all poha
             , hr_all_organization_units hou
        WHERE  poha.po_header_id         = p_doc_id
          AND  poha.latest_external_flag = 'Y'
          AND  hou.organization_id       = poha.org_id;
Line: 497

        update_order_otm_status
        ( p_doc_id => p_doc_id,
          p_doc_type => p_doc_type,
          p_order_otm_status => 'Business Event Failure',
          p_otm_recovery_flag => 'Y'
        );
Line: 510

        update_order_otm_status
        ( p_doc_id => p_doc_id,
          p_doc_type => p_doc_type,
          p_order_otm_status => 'In Advanced Queue',
          p_otm_recovery_flag => 'N'
        );
Line: 555

END handle_doc_update;
Line: 592

update_order_otm_status
( p_doc_id => p_doc_id,
  p_doc_type => p_doc_type,
  p_order_otm_status =>  'IN BPEL Processing',
  p_otm_recovery_flag => 'N'
);
Line: 761

SELECT ftb.timezone_code
INTO   x_otm_doc.server_timezone_code
FROM   fnd_timezones_b ftb
WHERE  ftb.upgrade_tz_id = FND_PROFILE.value('SERVER_TIMEZONE_ID');
Line: 796

  SELECT pola.po_line_id
       , plla.line_location_id
       , pola.line_num
       , plla.shipment_num
       , plla.quantity
       , plla.quantity_cancelled quantity_canceled
       , plla.price_override
       , pola.item_description
       , msik.concatenated_segments item
       , pola.item_revision
       , pola.vendor_product_num supplier_item_id
       , pola.supplier_ref_number supplier_config_id
       , NVL(muom.attribute15, muom.uom_code) uom
       , poha.currency_code
       , pola.order_type_lookup_code
       , plla.need_by_date
       , plla.promised_date
       , NVL(plla.days_early_receipt_allowed, 0)
       , NVL(plla.days_late_receipt_allowed, 0)
       , plla.ship_to_organization_id
       , hou.name ship_to_org_name
       , plla.drop_ship_flag
       , plla.ship_to_location_id
       , hrl.location_code ship_to_location_code
       , TRIM(ppf.first_name || ' ' || ppf.last_name) ship_to_contact_name
       , ppf.email_address ship_to_contact_email
       , HR_GENERAL.get_phone_number(
           psg.deliver_to_person_id, 'W1', SYSDATE) ship_to_contact_phone
       , HR_GENERAL.get_phone_number(
           psg.deliver_to_person_id, 'W1', SYSDATE) ship_to_contact_fax
  FROM   po_headers_archive_all        poha
       , po_lines_archive_all          pola
       , po_line_locations_archive_all plla
       , hr_all_organization_units     hou
       , hr_locations_all              hrl
       , mtl_system_items_kfv          msik
       , mtl_units_of_measure          muom
       , financials_system_params_all  fsp
       , per_all_people_f              ppf
       , ( SELECT psg.index_num1 line_location_id
                , psg.num1 deliver_to_person_id
           FROM   po_session_gt psg
           WHERE  psg.key = p_gt_key ) psg
  WHERE  poha.po_header_id                =  p_doc_id
    AND  poha.revision_num                =  p_doc_revision
    AND  pola.po_header_id                = poha.po_header_id
    AND  pola.revision_num                =
                              ( SELECT MAX(pola2.revision_num)
                                FROM   po_lines_archive_all pola2
                                WHERE  pola2.po_line_id   = pola.po_line_id
                                  AND  pola2.revision_num <= poha.revision_num )
    AND  plla.po_line_id                  = pola.po_line_id
    AND  plla.revision_num                =
                              ( SELECT MAX(plla2.revision_num)
                                FROM   po_line_locations_archive_all plla2
                                WHERE  plla2.line_location_id = plla.line_location_id
                                 AND   plla2.revision_num <= poha.revision_num )
    AND  psg.line_location_id (+)         = plla.line_location_id
    AND  ppf.person_id (+)                = psg.deliver_to_person_id
    AND  TRUNC(SYSDATE)
         BETWEEN  ppf.effective_start_date (+) AND ppf.effective_end_date (+)
    AND  hou.organization_id              =  plla.ship_to_organization_id
    AND  hrl.location_id (+)              =  plla.ship_to_location_id
    AND  NVL(fsp.org_id, -99)             =  NVL(pola.org_id, -99)
    AND  msik.inventory_item_id (+)       =  pola.item_id
    AND  NVL(msik.organization_id,
           fsp.inventory_organization_id) = fsp.inventory_organization_id
    AND  muom.unit_of_measure             =  pola.unit_meas_lookup_code
    AND  pola.order_type_lookup_code      =  'QUANTITY'
    AND  NVL(msik.outside_operation_flag, 'N') = 'N'
    AND  plla.approved_flag               =  'Y'
    AND  NVL(plla.cancel_flag, 'N')       <> 'Y';
Line: 890

SELECT poha.po_header_id
     , poha.segment1
     , poha.freight_terms_lookup_code
     , poha.shipping_control
     , poha.vendor_id
     , poha.vendor_site_id
     , pov.vendor_name
     , povs.address_line1
     , povs.address_line2
     , povs.address_line3
     , povs.city
     , fter.iso_territory_code
     , povs.vendor_site_code
     , povs.zip
     , DECODE(povs.state, NULL,
              DECODE(povs.province, NULL, povs.county, povs.province), povs.state)
     , povc.prefix
     , povc.first_name
     , povc.middle_name
     , povc.last_name
     , povc.area_code || povc.phone
     , povc.email_address
     , povc.fax_area_code || povc.fax
     , poha.org_id
     , hou.name
     , hrl.location_id
     , hrl.location_code
     , ppf.first_name
     , ppf.last_name
     , hr_general.get_phone_number(poha.agent_id, 'W1', SYSDATE)
     , ppf.email_address
     , hr_general.get_phone_number(poha.agent_id, 'WF', SYSDATE)
     , poha.bill_to_location_id
     , hrl2.location_code
     , apt.name -- terms
INTO   x_otm_doc.po_header_id
     , x_otm_doc.po_number
     , x_otm_doc.freight_terms_lookup_code
     , x_otm_doc.shipping_control
     , x_otm_doc.supplier_id
     , x_otm_doc.supplier_site_id
     , x_otm_doc.supplier_name
     , x_otm_doc.supplier_addr_line_1
     , x_otm_doc.supplier_addr_line_2
     , x_otm_doc.supplier_addr_line_3
     , x_otm_doc.supplier_addr_city
     , x_otm_doc.supplier_addr_country
     , x_otm_doc.supplier_site_code
     , x_otm_doc.supplier_addr_zip
     , x_otm_doc.supplier_addr_state_province
     , x_otm_doc.supplier_contact_prefix
     , x_otm_doc.supplier_contact_first_name
     , x_otm_doc.supplier_contact_middle_name
     , x_otm_doc.supplier_contact_last_name
     , x_otm_doc.supplier_contact_phone
     , x_otm_doc.supplier_contact_email
     , x_otm_doc.supplier_contact_fax
     , x_otm_doc.org_id
     , x_otm_doc.org_name
     , x_otm_doc.org_location_id
     , x_otm_doc.org_location_code
     , x_otm_doc.buyer_first_name
     , x_otm_doc.buyer_last_name
     , x_otm_doc.buyer_phone
     , x_otm_doc.buyer_email
     , x_otm_doc.buyer_fax
     , x_otm_doc.bill_to_location_id
     , x_otm_doc.bill_to_location_code
     , x_otm_doc.terms
FROM   po_headers_archive_all       poha
     , po_vendors                   pov
     , po_vendor_sites_all          povs
     , fnd_territories              fter
     , po_vendor_contacts           povc
     , hr_all_organization_units    hou
     , hr_locations_all             hrl
     , per_all_people_f             ppf
     , hr_locations_all             hrl2
     , ap_terms                     apt
WHERE  poha.po_header_id                         =  p_doc_id
  AND  poha.revision_num                         =  p_doc_revision
  AND  poha.vendor_id                            =  pov.vendor_id
  AND  poha.vendor_site_id                       =  povs.vendor_site_id
  AND  fter.territory_code (+)                   =  povs.country
  AND  poha.vendor_contact_id                    =  povc.vendor_contact_id (+)
  AND  povs.vendor_site_id                        = NVL(povc.vendor_site_id,povs.vendor_site_id) /*bug 7173062, added the condition
  to eliminate duplicate rows being returned when the same contact is assigned for different supplier sites */
  AND  poha.org_id                               =  hou.organization_id
  AND  hrl.location_id                           =  hou.location_id
  AND  ppf.person_id                             =  poha.agent_id
  AND  trunc(sysdate)
    BETWEEN  ppf.effective_start_date (+) AND ppf.effective_end_date (+)
  AND  hrl2.location_id                          =  poha.bill_to_location_id
  AND  apt.term_id (+)                           =  poha.terms_id
  AND  poha.authorization_status                 =  'APPROVED'
  AND  NVL(poha.consigned_consumption_flag, 'N') =  'N'
;
Line: 1015

INSERT INTO po_session_gt
( key
, index_num1 -- line_location_id
, index_num2 -- distribution_num
, num1       -- deliver_to_person_id
)
( SELECT l_gt_key1
       , poda.line_location_id
       , poda.distribution_num
       , poda.deliver_to_person_id
  FROM   po_line_locations_archive_all plla
       , po_distributions_archive_all poda
  WHERE  plla.po_header_id         = p_doc_id
    AND  plla.revision_num         = ( SELECT MAX(plla2.revision_num)
                                       FROM
                                       po_line_locations_archive_all plla2
                                       WHERE plla2.line_location_id
                                                   = plla.line_location_id
                                         AND plla2.po_header_id = p_doc_id
                                         AND plla2.revision_num <= p_doc_revision )
    AND  poda.line_location_id     = plla.line_location_id
    AND  poda.revision_num         = ( SELECT MAX(poda2.revision_num)
                                       FROM po_distributions_archive_all poda2
                                       WHERE poda2.po_distribution_id
                                                     = poda.po_distribution_id
                                         AND poda2.line_location_id = plla.line_location_id
                                         AND poda2.revision_num <= p_doc_revision )
    AND  NVL(plla.cancel_flag,'N') <> 'Y'
    AND  poda.deliver_to_person_id IS NOT NULL
)
;
Line: 1054

INSERT INTO po_session_gt
( key
, index_num1 -- line_location_id
, num1       -- deliver_to_person_id
)
( SELECT l_gt_key2
       , psg.index_num1
       , psg.num1
  FROM   po_session_gt psg
       , ( SELECT MIN(psg2.index_num2) distribution_num
                , psg2.index_num1 line_location_id
           FROM   po_session_gt psg2
           WHERE  psg2.key = l_gt_key1
           GROUP BY psg2.index_num1 ) min_dists
  WHERE  psg.key        = l_gt_key1
    AND  psg.index_num1 = min_dists.line_location_id
    AND  psg.index_num2 = min_dists.distribution_num
)
;
Line: 1337

  SELECT pola.po_line_id
       , plla.line_location_id
       , pola.line_num
       , plla.shipment_num
  FROM   po_headers_archive_all        poha
       , po_lines_archive_all          pola
       , po_line_locations_archive_all plla
       , financials_system_params_all  fsp
       , mtl_system_items              msi
  WHERE  poha.po_header_id                        =  p_doc_id
    AND  poha.revision_num                        =  p_doc_revision
    AND  pola.po_header_id                        =  poha.po_header_id
    AND  pola.revision_num                        =
                              ( SELECT MAX(pola2.revision_num)
                                FROM   po_lines_archive_all pola2
                                WHERE  pola2.po_line_id   = pola.po_line_id
                                  AND  pola2.revision_num <= poha.revision_num )
    AND  plla.po_line_id                          =  pola.po_line_id
    AND  plla.revision_num                        =
                              ( SELECT MAX(plla2.revision_num)
                                FROM   po_line_locations_archive_all plla2
                                WHERE  plla2.line_location_id = plla.line_location_id
                                 AND   plla2.revision_num <= poha.revision_num )
    AND  pola.order_type_lookup_code               =  'QUANTITY'
    AND  fsp.org_id                                =  pola.org_id
    AND  msi.inventory_item_id (+)                 =  pola.item_id
    AND  NVL(msi.organization_id,
           fsp.inventory_organization_id)          =  fsp.inventory_organization_id
    AND  NVL(msi.outside_operation_flag, 'N')      =  'N'
    AND  NVL(p_line_id, pola.po_line_id)           =  pola.po_line_id
    AND  NVL(p_line_loc_id, plla.line_location_id) =  plla.line_location_id
;
Line: 1390

SELECT poha.po_header_id
     , poha.segment1
     , hou.name
INTO   x_otm_doc.po_header_id
     , x_otm_doc.po_number
     , x_otm_doc.org_name
FROM   po_headers_archive_all       poha
     , hr_all_organization_units    hou
WHERE  poha.po_header_id                         =  p_doc_id
  AND  poha.revision_num                         =  p_doc_revision
  AND  poha.org_id                               =  hou.organization_id
  AND  NVL(poha.consigned_consumption_flag, 'N')  =  'N'
;
Line: 1501

SELECT poha.po_header_id
     , poha.segment1
     , hou.name
INTO   x_otm_doc.po_header_id
     , x_otm_doc.po_number
     , x_otm_doc.org_name
FROM   po_headers_archive_all       poha
     , hr_all_organization_units    hou
WHERE  poha.po_header_id                         =  p_doc_id
  AND  poha.revision_num                         =  p_doc_revision
  AND  poha.org_id                               =  hou.organization_id
  AND  NVL(poha.consigned_consumption_flag, 'N') =  'N'
;
Line: 1551

  SELECT pola.po_line_id
       , plla.line_location_id
       , pola.line_num
       , plla.shipment_num
       , plla.quantity
       , plla.quantity_cancelled quantity_canceled
       , plla.price_override
       , pola.item_description
       , msik.concatenated_segments item
       , pola.item_revision
       , pola.vendor_product_num supplier_item_id
       , pola.supplier_ref_number supplier_config_id
       , NVL(muom.attribute15, muom.uom_code) uom
       , poha.currency_code
       , pola.order_type_lookup_code
       , plla.need_by_date
       , plla.promised_date
       , NVL(plla.days_early_receipt_allowed, 0)
       , NVL(plla.days_late_receipt_allowed, 0)
       , plla.ship_to_organization_id
       , hou.name ship_to_org_name
       , plla.drop_ship_flag
       , plla.ship_to_location_id
       , hrl.location_code ship_to_location_code
       , TRIM(ppf.first_name || ' ' || ppf.last_name) ship_to_contact_name
       , ppf.email_address ship_to_contact_email
       , HR_GENERAL.get_phone_number(
           psg.deliver_to_person_id, 'W1', SYSDATE) ship_to_contact_phone
       , HR_GENERAL.get_phone_number(
           psg.deliver_to_person_id, 'W1', SYSDATE) ship_to_contact_fax
  FROM   po_headers_archive_all        poha
       , po_releases_archive_all       pora
       , po_lines_archive_all          pola
       , po_line_locations_archive_all plla
       , hr_all_organization_units     hou
       , hr_locations_all              hrl
       , mtl_system_items_kfv          msik
       , mtl_units_of_measure          muom
       , financials_system_params_all  fsp
       , per_all_people_f              ppf
       , ( SELECT psg.index_num1 line_location_id
                , psg.num1 deliver_to_person_id
           FROM   po_session_gt psg
           WHERE  psg.key = p_gt_key ) psg
  WHERE  pora.po_release_id               =  p_doc_id
    AND  pora.revision_num                =  p_doc_revision
    AND  poha.po_header_id                =  pora.po_header_id
    AND  poha.revision_num                =  p_blanket_revision
    AND  pola.po_header_id                =  poha.po_header_id
    AND  pola.revision_num                =
                              ( SELECT MAX(pola2.revision_num)
                                FROM   po_lines_archive_all pola2
                                WHERE  pola2.po_line_id   = pola.po_line_id
                                  AND  pola2.revision_num <= poha.revision_num )
    AND  plla.po_line_id                  = pola.po_line_id
    AND  plla.po_release_id               = pora.po_release_id
    AND  plla.revision_num                =
                              ( SELECT MAX(plla2.revision_num)
                                FROM   po_line_locations_archive_all plla2
                                WHERE  plla2.line_location_id = plla.line_location_id
                                 AND   plla2.revision_num <= pora.revision_num )
    AND  psg.line_location_id (+)         = plla.line_location_id
    AND  ppf.person_id (+)                = psg.deliver_to_person_id
    AND  TRUNC(SYSDATE)
         BETWEEN  ppf.effective_start_date (+) AND ppf.effective_end_date (+)
    AND  hou.organization_id              =  plla.ship_to_organization_id
    AND  hrl.location_id (+)              =  plla.ship_to_location_id
    AND  NVL(fsp.org_id, -99)             =  NVL(pola.org_id, -99)
    AND  msik.inventory_item_id (+)       =  pola.item_id
    AND  NVL(msik.organization_id,
           fsp.inventory_organization_id) = fsp.inventory_organization_id
    AND  muom.unit_of_measure             =  pola.unit_meas_lookup_code
    AND  pola.order_type_lookup_code      =  'QUANTITY'
    AND  NVL(msik.outside_operation_flag, 'N') = 'N'
    AND  plla.approved_flag               =  'Y'
    AND  NVL(plla.cancel_flag, 'N')       <> 'Y';
Line: 1650

SELECT poha.po_header_id
     , pora.po_release_id
     , poha.segment1
     , pora.release_num
     , poha.freight_terms_lookup_code
     , pora.shipping_control
     , poha.vendor_id
     , poha.vendor_site_id
     , pov.vendor_name
     , povs.address_line1
     , povs.address_line2
     , povs.address_line3
     , povs.city
     , fter.iso_territory_code
     , povs.vendor_site_code
     , povs.zip
     , DECODE(povs.state, NULL,
              DECODE(povs.province, NULL, povs.county, povs.province), povs.state)
     , povc.prefix
     , povc.first_name
     , povc.middle_name
     , povc.last_name
     , povc.area_code || povc.phone
     , povc.email_address
     , povc.fax_area_code || povc.fax
     , poha.org_id
     , hou.name
     , hrl.location_id
     , hrl.location_code
     , ppf.first_name
     , ppf.last_name
     , hr_general.get_phone_number(poha.agent_id, 'W1', SYSDATE)
     , ppf.email_address
     , hr_general.get_phone_number(poha.agent_id, 'WF', SYSDATE)
     , poha.bill_to_location_id
     , hrl2.location_code
     , apt.name -- terms
INTO   x_otm_doc.po_header_id
     , x_otm_doc.po_release_id
     , x_otm_doc.po_number
     , x_otm_doc.release_number
     , x_otm_doc.freight_terms_lookup_code
     , x_otm_doc.shipping_control
     , x_otm_doc.supplier_id
     , x_otm_doc.supplier_site_id
     , x_otm_doc.supplier_name
     , x_otm_doc.supplier_addr_line_1
     , x_otm_doc.supplier_addr_line_2
     , x_otm_doc.supplier_addr_line_3
     , x_otm_doc.supplier_addr_city
     , x_otm_doc.supplier_addr_country
     , x_otm_doc.supplier_site_code
     , x_otm_doc.supplier_addr_zip
     , x_otm_doc.supplier_addr_state_province
     , x_otm_doc.supplier_contact_prefix
     , x_otm_doc.supplier_contact_first_name
     , x_otm_doc.supplier_contact_middle_name
     , x_otm_doc.supplier_contact_last_name
     , x_otm_doc.supplier_contact_phone
     , x_otm_doc.supplier_contact_email
     , x_otm_doc.supplier_contact_fax
     , x_otm_doc.org_id
     , x_otm_doc.org_name
     , x_otm_doc.org_location_id
     , x_otm_doc.org_location_code
     , x_otm_doc.buyer_first_name
     , x_otm_doc.buyer_last_name
     , x_otm_doc.buyer_phone
     , x_otm_doc.buyer_email
     , x_otm_doc.buyer_fax
     , x_otm_doc.bill_to_location_id
     , x_otm_doc.bill_to_location_code
     , x_otm_doc.terms
FROM   po_headers_archive_all       poha
     , po_releases_archive_all      pora
     , po_vendors                   pov
     , po_vendor_sites_all          povs
     , fnd_territories              fter
     , po_vendor_contacts           povc
     , hr_all_organization_units    hou
     , hr_locations_all             hrl
     , per_all_people_f             ppf
     , hr_locations_all             hrl2
     , ap_terms                     apt
WHERE  pora.po_release_id                        =  p_doc_id
  AND  pora.revision_num                         =  p_doc_revision
  AND  poha.po_header_id                         =  pora.po_header_id
  AND  poha.revision_num                         =  p_blanket_revision
  AND  poha.vendor_id                            =  pov.vendor_id
  AND  poha.vendor_site_id                       =  povs.vendor_site_id
  AND  fter.territory_code (+)                   =  povs.country
  AND  poha.vendor_contact_id                    =  povc.vendor_contact_id (+)
  AND  povs.vendor_site_id                        = NVL(povc.vendor_site_id,povs.vendor_site_id) /*bug 7173062, added the condition to
  eliminate duplicate rows being returned when the same contact is assigned for different supplier sites */
  AND  poha.org_id                               =  hou.organization_id
  AND  hrl.location_id                           =  hou.location_id
  AND  ppf.person_id                             =  pora.agent_id
  AND  trunc(sysdate)
    BETWEEN  ppf.effective_start_date (+) AND ppf.effective_end_date (+)
  AND  hrl2.location_id                          =  poha.bill_to_location_id
  AND  apt.term_id (+)                           =  poha.terms_id
  AND  poha.authorization_status                 =  'APPROVED'
  AND  NVL(poha.consigned_consumption_flag, 'N') =  'N'
;
Line: 1782

INSERT INTO po_session_gt
( key
, index_num1 -- line_location_id
, index_num2 -- distribution_num
, num1       -- deliver_to_person_id
)
( SELECT l_gt_key1
       , poda.line_location_id
       , poda.distribution_num
       , poda.deliver_to_person_id
  FROM   po_line_locations_archive_all plla
       , po_distributions_archive_all poda
  WHERE  plla.po_release_id        = p_doc_id
    AND  plla.revision_num         = ( SELECT MAX(plla2.revision_num)
                                       FROM
                                       po_line_locations_archive_all plla2
                                       WHERE plla2.line_location_id
                                                   = plla.line_location_id
                                         AND plla2.po_release_id = p_doc_id
                                         AND plla2.revision_num <= p_doc_revision )
    AND  poda.line_location_id     = plla.line_location_id
    AND  poda.revision_num         = ( SELECT MAX(poda2.revision_num)
                                       FROM po_distributions_archive_all poda2
                                       WHERE poda2.po_distribution_id
                                                     = poda.po_distribution_id
                                         AND poda2.line_location_id = plla.line_location_id
                                         AND poda2.revision_num <= p_doc_revision )
    AND  NVL(plla.cancel_flag,'N') <> 'Y'
    AND  poda.deliver_to_person_id IS NOT NULL
)
;
Line: 1821

INSERT INTO po_session_gt
( key
, index_num1 -- line_location_id
, num1       -- deliver_to_person_id
)
( SELECT l_gt_key2
       , psg.index_num1
       , psg.num1
  FROM   po_session_gt psg
       , ( SELECT MIN(psg2.index_num2) distribution_num
                , psg2.index_num1 line_location_id
           FROM   po_session_gt psg2
           WHERE  psg2.key = l_gt_key1
           GROUP BY psg2.index_num1 ) min_dists
  WHERE  psg.key        = l_gt_key1
    AND  psg.index_num1 = min_dists.line_location_id
    AND  psg.index_num2 = min_dists.distribution_num
)
;
Line: 2104

  SELECT pola.po_line_id
       , plla.line_location_id
       , pola.line_num
       , plla.shipment_num
  FROM   po_headers_archive_all        poha
       , po_releases_archive_all       pora
       , po_lines_archive_all          pola
       , po_line_locations_archive_all plla
       , mtl_system_items              msi
       , financials_system_params_all  fsp
  WHERE  pora.po_release_id                       =  p_doc_id
    AND  pora.revision_num                        =  p_doc_revision
    AND  poha.po_header_id                        =  pora.po_header_id
    AND  poha.revision_num                        =  p_blanket_revision
    AND  pola.po_header_id                        =  poha.po_header_id
    AND  pola.revision_num                        =
                              ( SELECT MAX(pola2.revision_num)
                                FROM   po_lines_archive_all pola2
                                WHERE  pola2.po_line_id   = pola.po_line_id
                                  AND  pola2.po_header_id = poha.po_header_id
                                  AND  pola2.revision_num <= poha.revision_num )
    AND  plla.po_line_id                          =  pola.po_line_id
    AND  plla.po_release_id                       =  pora.po_release_id
    AND  plla.revision_num                        =
                              ( SELECT MAX(plla2.revision_num)
                                FROM   po_line_locations_archive_all plla2
                                WHERE  plla2.line_location_id = plla.line_location_id
                                 AND   plla2.po_line_id       = pola.po_line_id
                                 AND   plla2.revision_num     <= pora.revision_num )
    AND  pola.order_type_lookup_code              =  'QUANTITY'
    AND  fsp.org_id                               =  pola.org_id
    AND  msi.inventory_item_id (+)                =  pola.item_id
    AND  NVL(msi.organization_id,
           fsp.inventory_organization_id)         =  fsp.inventory_organization_id
    AND  NVL(msi.outside_operation_flag, 'N')     =  'N'
    AND  NVL(p_line_loc_id, plla.line_location_id) =  plla.line_location_id;
Line: 2161

SELECT poha.po_header_id
     , pora.po_release_id
     , poha.segment1
     , pora.release_num
     , hou.name
INTO   x_otm_doc.po_header_id
     , x_otm_doc.po_release_id
     , x_otm_doc.po_number
     , x_otm_doc.release_number
     , x_otm_doc.org_name
FROM   po_headers_archive_all       poha
     , po_releases_archive_all      pora
     , hr_all_organization_units    hou
WHERE  pora.po_release_id                        =  p_doc_id
  AND  pora.revision_num                         =  p_doc_revision
  AND  poha.po_header_id                         =  pora.po_header_id
  AND  poha.revision_num                         =  p_blanket_revision
  AND  poha.org_id                               =  hou.organization_id
  AND  NVL(poha.consigned_consumption_flag, 'N') =  'N'
;
Line: 2277

SELECT poha.po_header_id
     , por.po_release_id
     , poha.segment1
     , por.release_num
     , hou.name
INTO   x_otm_doc.po_header_id
     , x_otm_doc.po_release_id
     , x_otm_doc.po_number
     , x_otm_doc.release_number
     , x_otm_doc.org_name
FROM   po_headers_archive_all       poha
     , po_releases_all              por
     , hr_all_organization_units    hou
WHERE  por.po_release_id                         =  p_doc_id
  AND  poha.po_header_id                         =  por.po_header_id
  AND  poha.revision_num                         =  p_blanket_revision
  AND  poha.org_id                               =  hou.organization_id
  AND  NVL(poha.consigned_consumption_flag, 'N') =  'N'
;
Line: 2586

  SELECT po_header_id
  FROM   po_headers_all POH
  WHERE  POH.otm_recovery_flag = 'Y';
Line: 2591

  SELECT po_release_id
  FROM   po_releases_all POR
  WHERE  POR.otm_recovery_flag = 'Y';
Line: 2634

    update_order_otm_status
    ( p_doc_id => l_failed_doc_tbl(i),
      p_doc_type => 'PO',
      p_order_otm_status => NULL,
      p_otm_recovery_flag => 'R'
    );
Line: 2643

    PO_OTM_INTEGRATION_PVT.handle_doc_update
    ( p_doc_type => 'PO',
      p_doc_id => l_failed_doc_tbl(i),
      p_action => 'APPROVE',
      p_line_id => null,
      p_line_loc_id => null
    );
Line: 2653

    PO_OTM_INTEGRATION_PVT.handle_doc_update
    ( p_doc_type => 'PO',
      p_doc_id => l_failed_doc_tbl(i),
      p_action => 'RECOVER',
      p_line_id => null,
      p_line_loc_id => null
    );
Line: 2663

  l_failed_doc_tbl.DELETE;
Line: 2689

    update_order_otm_status
    ( p_doc_id => l_failed_doc_tbl(i),
      p_doc_type => 'RELEASE',
      p_order_otm_status => NULL,
      p_otm_recovery_flag => 'R'
    );
Line: 2698

    PO_OTM_INTEGRATION_PVT.handle_doc_update
    ( p_doc_type => 'RELEASE',
      p_doc_id => l_failed_doc_tbl(i),
      p_action => 'APPROVE',
      p_line_id => null,
      p_line_loc_id => null
    );
Line: 2708

    PO_OTM_INTEGRATION_PVT.handle_doc_update
    ( p_doc_type => 'RELEASE',
      p_doc_id => l_failed_doc_tbl(i),
      p_action => 'RECOVER',
      p_line_id => null,
      p_line_loc_id => null
    );
Line: 2747

  SELECT POL.po_line_id,
         PLL.line_location_id,
         POL.line_num,
         PLL.shipment_num,
         NVL(PLL.cancel_flag, 'N'),
         NVL(PLL.closed_code, 'OPEN')
  FROM   po_lines_all POL,
         po_line_locations_all PLL,
         mtl_system_items MSI
  WHERE  POL.po_header_id = p_doc_id
  AND    PLL.po_line_id = POL.po_line_id
  AND    POL.order_type_lookup_code = 'QUANTITY'
  AND    MSI.inventory_item_id(+) = POL.item_id
  AND    MSI.organization_id(+) = p_inv_org_id
  AND    NVL(MSI.outside_operation_flag, 'N') = 'N';
Line: 2781

  SELECT POH.po_header_id,
         POH.segment1,
         HOU.name,
         NVL(POH.cancel_flag, 'N'),
         NVL(POH.closed_code, 'OPEN'),
         NVL(POH.user_hold_flag, 'N'),
         FSP.inventory_organization_id
  INTO   x_otm_doc.po_header_id,
         x_otm_doc.po_number,
         x_otm_doc.org_name,
         l_header_cancel_flag,
         l_header_closed_code,
         l_header_user_hold_flag,
         l_inv_org_id
  FROM   po_headers_all POH,
         hr_all_organization_units HOU,
         financials_system_params_all FSP
  WHERE  POH.po_header_id = p_doc_id
  AND    POH.org_id = HOU.organization_id
  AND    POH.org_id = FSP.org_id
  AND    NVL(POH.consigned_consumption_flag, 'N') = 'N';
Line: 2902

  SELECT POL.po_line_id,
         PLL.line_location_id,
         POL.line_num,
         PLL.shipment_num,
         NVL(PLL.cancel_flag, 'N'),
         NVL(PLL.closed_code, 'OPEN')
  FROM   po_releases_all POR,
          po_lines_all POL,
         po_line_locations_all PLL,
         mtl_system_items MSI
  WHERE  POR.po_release_id = p_doc_id
  AND    POL.po_header_id = POR.po_header_id
  AND    PLL.po_release_id = POR.po_release_id
  AND    PLL.po_line_id = POL.po_line_id
  AND    POL.order_type_lookup_code = 'QUANTITY'
  AND    MSI.inventory_item_id(+) = POL.item_id
  AND    MSI.organization_id(+) = p_inv_org_id
  AND    NVL(MSI.outside_operation_flag, 'N') = 'N';
Line: 2939

  SELECT POH.po_header_id,
         POR.po_release_id,
         POH.segment1,
         POR.release_num,
         HOU.name,
         NVL(POR.cancel_flag, 'N'),
         NVL(POR.closed_code, 'OPEN'),
         NVL(POR.hold_flag, 'N'),
         FSP.inventory_organization_id
  INTO   x_otm_doc.po_header_id,
         x_otm_doc.po_release_id,
         x_otm_doc.po_number,
         x_otm_doc.release_number,
         x_otm_doc.org_name,
         l_header_cancel_flag,
         l_header_closed_code,
         l_header_user_hold_flag,
         l_inv_org_id
  FROM   po_releases_all POR,
         po_headers_all POH,
         hr_all_organization_units HOU,
         financials_system_params_all FSP
  WHERE  POR.po_release_id = p_doc_id
  AND    POR.po_header_id = POH.po_header_id
  AND    POR.org_id = HOU.organization_id
  AND    POR.org_id = FSP.org_id
  AND    NVL(POR.consigned_consumption_flag, 'N') = 'N';
Line: 3052

PROCEDURE update_order_otm_status
( p_doc_id IN NUMBER,
  p_doc_type IN VARCHAR2,
  p_order_otm_status IN VARCHAR2,
  p_otm_recovery_flag IN VARCHAR2
) IS

d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'update_order_otm_status';
Line: 3080

    UPDATE po_releases_all POR
    SET POR.otm_status_code = p_order_otm_status,
        POR.otm_recovery_flag = DECODE (p_otm_recovery_flag,
                                        'R', null,
                                        'N', POR.otm_recovery_flag,
                                        p_otm_recovery_flag)
    WHERE POR.po_release_id = p_doc_id;
Line: 3088

    UPDATE po_headers_all POH
    SET    POH.otm_status_code = p_order_otm_status,
           POH.otm_recovery_flag = DECODE (p_otm_recovery_flag,
                                           'R', null,
                                           'N', POH.otm_recovery_flag,
                                           p_otm_recovery_flag)
    WHERE  POH.po_header_id = p_doc_id;
Line: 3110

END update_order_otm_status;