[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(shipment_num) + 1
into l_shipment_number
from po_line_locations_archive_all
where po_line_id = p_line_id
and latest_external_flag = 'Y';
select org_id
into x_org_id
from po_releases_all
where po_release_id= p_po_release_id;
select type_lookup_code into v_type_code
from po_headers_all
where po_header_id= p_po_header_id;
select org_id
into x_org_id
from po_headers_all
where po_header_id= p_po_header_id;
select plla.PO_LINE_ID into l_doc_line_id
from po_line_locations_all plla
where plla.line_location_id = p_po_line_location_id;
SELECT org_id
into l_org_id
FROM po_vendor_sites_all
WHERE vendor_id = p_tp_id
AND vendor_site_id = p_tp_site_id;
select po_header_id, VENDOR_ORDER_NUM
into l_po_header_id, l_old_so_order_number
from po_headers_all
where segment1 = p_po_number
AND org_id = l_org_id
AND TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
SELECT org_id
into l_org_id
FROM po_vendor_sites_all
WHERE vendor_id = p_tp_id
AND vendor_site_id = p_tp_site_id;
select po_header_id into l_po_header_id
from po_headers_all
where segment1 = p_po_number
AND org_id = l_org_id
AND TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
select po_release_id into l_release_id
from po_releases_all
where po_header_id = l_po_header_id and release_num = p_release_number;
select max(REVISION_NUM)
into l_max_rev
from po_headers_archive_all
where PO_HEADER_ID = l_po_header_id;
SELECT org_id
into l_org_id
FROM po_vendor_sites_all
WHERE vendor_id = p_tp_id
AND vendor_site_id = p_tp_site_id;
select
PLA.PO_LINE_ID, PHA.PO_HEADER_ID,
FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY,
PLA.UNIT_PRICE UNIT_PRICE,
PLA.CLOSED_CODE,PLA.CLOSED_FLAG, PLA.CANCEL_FLAG, PLA.VENDOR_PRODUCT_NUM,
POCR.REQUEST_STATUS, PHA.CURRENCY_CODE
into l_line_id, l_po_header_id,
l_old_price_currency, l_old_price,
l_closed_code, l_closed_flag, l_cancel_flag, l_old_supplier_part_num,
l_req_status, l_po_curr
FROM
PO_LINES_ALL PLA,
FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
PO_HEADERS_ALL PHA,
PO_CHANGE_REQUESTS POCR
WHERE
FSPA.ORG_ID = PLA.ORG_ID AND
pha.po_header_id = PLA.PO_HEADER_ID and
pha.segment1 = p_po_number and
pha.org_id = l_org_id AND
pha.TYPE_LOOKUP_CODE = 'STANDARD' and
PLA.line_num = p_line_num and
PLA.PO_HEADER_ID = POCR.DOCUMENT_HEADER_ID (+) AND
PLA.PO_LINE_ID = POCR.DOCUMENT_LINE_ID (+) AND
POCR.REQUEST_LEVEL (+)= 'LINE' AND
POCR.CHANGE_ACTIVE_FLAG (+)= 'Y';
select max(REVISION_NUM)
into l_max_rev
from po_headers_archive_all
where PO_HEADER_ID = l_po_header_id;
SELECT org_id
into l_org_id
FROM po_vendor_sites_all
WHERE vendor_id = p_tp_id
AND vendor_site_id = p_tp_site_id;
select
PLLA.po_release_id, PLLA.LINE_LOCATION_ID,
PLLA.PO_LINE_ID, PHA.PO_HEADER_ID,
(PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED) ORDERED_QUANTITY,
PLLA.QUANTITY_RECEIVED, PLLA.QUANTITY_BILLED,
--MUOMTL.UOM_CODE UOM,
FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY,
NVL(PLLA.PROMISED_DATE, PLLA.NEED_BY_DATE) PROMISED_DATE,
PLLA.PRICE_OVERRIDE PRICE_OVERRIDE,
PLLA.CLOSED_CODE,PLLA.CLOSED_FLAG, PLLA.SUPPLIER_ORDER_LINE_NUMBER,
POCR.REQUEST_STATUS, PLA.ALLOW_PRICE_OVERRIDE_FLAG,
SHIPMENT_TYPE, DROP_SHIP_FLAG, PHA.CURRENCY_CODE
into l_release_id, l_line_location_id, l_line_id, l_po_header_id,
l_old_quantity, l_qt_recieved, l_qt_billed,
--l_old_uom,
l_old_price_currency,
l_old_promised_date, l_old_price,
l_closed_code, l_closed_flag, l_old_so_line_number,
l_req_status, l_line_price_ovrride,
l_shipment_type, l_drop_ship_flag, l_po_curr
FROM
PO_LINE_LOCATIONS_ALL PLLA,
PO_LINES_ALL PLA,
FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
-- MTL_UNITS_OF_MEASURE_TL MUOMTL,
PO_RELEASES_ALL PRAA, PO_HEADERS_ALL PHA,
PO_CHANGE_REQUESTS POCR
WHERE
PLA.PO_Line_id= PLLA.PO_Line_ID and
(PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED) > 0 AND
FSPA.ORG_ID = PLLA.ORG_ID AND
plla.po_release_id = praa.po_release_id and
pha.po_header_id = praa.po_header_id and
pha.segment1 = p_po_number and
pha.org_id = l_org_id AND
praa.release_num = p_release_number and
plla.shipment_num = l_shipment_number and
PLLA.PO_HEADER_ID = POCR.DOCUMENT_HEADER_ID (+) AND
PLLA.LINE_LOCATION_ID = POCR.DOCUMENT_LINE_LOCATION_ID (+) AND
POCR.REQUEST_LEVEL (+)= 'SHIPMENT' AND
POCR.CHANGE_ACTIVE_FLAG (+)= 'Y';
SELECT org_id
into l_org_id
FROM po_vendor_sites_all
WHERE vendor_id = p_tp_id
AND vendor_site_id = p_tp_site_id;
select
null, PLLA.LINE_LOCATION_ID, PLLA.PO_LINE_ID,
PHA.PO_HEADER_ID,
(PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED) ORDERED_QUANTITY,
PLLA.QUANTITY_RECEIVED, PLLA.QUANTITY_BILLED,
FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY,
NVL(PLLA.PROMISED_DATE, PLLA.NEED_BY_DATE) PROMISED_DATE,
PLLA.PRICE_OVERRIDE PRICE_OVERRIDE,
PLLA.CLOSED_CODE,PLLA.CLOSED_FLAG,
PLLA.SUPPLIER_ORDER_LINE_NUMBER,
POCR.REQUEST_STATUS,
SHIPMENT_TYPE, DROP_SHIP_FLAG, PHA.CURRENCY_CODE
into l_release_id, l_line_location_id, l_line_id,
l_po_header_id, l_old_quantity, l_qt_recieved,
l_qt_billed, l_old_price_currency,
l_old_promised_date, l_old_price,
l_closed_code, l_closed_flag, l_old_so_line_number,
l_req_status,
l_shipment_type, l_drop_ship_flag, l_po_curr
FROM
PO_LINE_LOCATIONS_ALL PLLA,
PO_LINES_ALL PLA,
FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
PO_HEADERS_ALL PHA,
PO_CHANGE_REQUESTS POCR
WHERE
PLA.PO_Line_id= PLLA.PO_Line_ID and
(PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED) > 0 AND
FSPA.ORG_ID = PLLA.ORG_ID AND
pha.po_header_id = PLLA.PO_HEADER_ID and
pha.segment1 = p_po_number and
pha.org_id = l_org_id AND
pha.TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ') and
plla.shipment_num = l_shipment_number and
PLA.line_num = p_line_num and
PLLA.PO_HEADER_ID = POCR.DOCUMENT_HEADER_ID (+) AND
PLLA.LINE_LOCATION_ID = POCR.DOCUMENT_LINE_LOCATION_ID (+) AND
POCR.REQUEST_LEVEL (+)= 'SHIPMENT' AND
POCR.CHANGE_ACTIVE_FLAG (+)= 'Y';
select max(REVISION_NUM)
into l_max_rev
from po_headers_archive_all
where PO_HEADER_ID = l_po_header_id;
select user_id
into l_user_id
from fnd_user
where user_name = p_requestor; --ideally it should be 'XML_USER'
g_po_change_table.delete;
SELECT org_id
into l_org_id
FROM po_vendor_sites_all
WHERE vendor_id = p_tp_id
AND vendor_site_id = p_tp_site_id;
select count(1) into l_count
from po_releases_all pra, po_headers_all pha
where pra.po_header_id = pha.po_header_id
and pra.release_num = p_release_number
and pha.segment1 = p_po_number
and pha.org_id = l_org_id
and pha.TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
select count(1) into l_count
from po_headers_all pha
where pha.segment1 = p_po_number
and pha.org_id = l_org_id
AND pha.TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
select authorization_status, po_header_id, last_update_date
into l_po_status, l_po_header_id, g_last_upd_date
from po_headers_all
where segment1 = p_po_number
and org_id = l_org_id
and TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
select authorization_status, po_release_id, last_update_date
into l_rel_status, l_release_id, g_last_upd_date
from po_releases_all
where po_header_id = l_po_header_id and release_num = p_release_number;
select user_id
into l_user_id
from fnd_user
where user_name = g_requestor;
l_last_update_date is added to check the concurrency when the same PO is modified in different places.
x_error is added to check if there are any concurrency issues. If x_error is true then it will raise
concurrency exception.
l_concurrency_exception is added to raise the exception in case if there are any concurrency issues. */
l_last_update_date po_headers_all.last_update_date%type;
SELECT org_id
into l_org_id
FROM po_vendor_sites_all
WHERE vendor_id = p_tp_id
AND vendor_site_id = p_tp_site_id;
select to_char(po_header_id), to_char(agent_id), LAST_UPDATE_DATE
into l_po_header_id, l_po_buyer_id, l_last_update_date
from po_headers_all
where segment1 = p_po_number
and org_id = l_org_id
AND TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
select to_char(user_id)
into l_user_id
from fnd_user
where user_name = p_requestor;
select to_char(po_release_id), to_char(agent_id), LAST_UPDATE_DATE
into l_po_release_id, l_po_buyer_id, l_last_update_date
from po_releases_all praa
where po_header_id = l_po_header_id
and RELEASE_NUM = p_release_number;
/* l_last_update_date is passed as an input parameter to check the
concurrency issue. x_error is an out variable, which captures errors
if there are any exceptions. */
POS_ACK_PO.ACKNOWLEDGE_PO (
l_po_header_id,
l_po_release_id,
l_po_buyer_id,
l_po_accept_reject,
l_po_acc_type_code, --Should be always null from FPI.
p_ack_reason,
l_user_id,
l_last_update_date,
x_error);
At this point the pl/sql table will be 'delete'd. Call this
procedure even if there were errors in the earlier calls.
*/
procedure windup_chn_ack_inbound (
p_requestor IN varchar2,
p_int_cont_num IN varchar2,
p_request_origin IN varchar2,
p_tp_id IN number,
p_tp_site_id IN number,
x_error_id_in IN number,
x_error_status_in IN varchar2,
x_error_id_out OUT NOCOPY number,
x_error_status_out OUT NOCOPY VARCHAR2
) is
begin
/* We have no use of these global variables; so erase them;
g_po_change_table.delete;