The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
d_module CONSTANT VARCHAR2(100) := g_module_prefix || 'HANDLE_DOC_UPDATE';
SELECT pol.order_type_lookup_code
INTO l_line_value_basis
FROM po_lines_all pol
WHERE pol.po_line_id = p_line_id;
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;
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;
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;
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;
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;
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'
);
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'
);
END handle_doc_update;
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'
);
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');
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';
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'
;
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
)
;
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
)
;
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
;
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'
;
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'
;
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';
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'
;
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
)
;
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
)
;
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;
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'
;
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'
;
SELECT po_header_id
FROM po_headers_all POH
WHERE POH.otm_recovery_flag = 'Y';
SELECT po_release_id
FROM po_releases_all POR
WHERE POR.otm_recovery_flag = 'Y';
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'
);
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
);
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
);
l_failed_doc_tbl.DELETE;
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'
);
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
);
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
);
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';
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';
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';
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';
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';
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;
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;
END update_order_otm_status;