The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT X_need_by_date_old,
X_need_by_date,
X_po_line_id
/* Bug 5610714 ,
X_shipment_num,
p_estimated_pickup_date,
p_ship_method */
BULK COLLECT INTO
l_need_by_dates_old,
l_need_by_dates,
l_po_line_ids
/* Bug 5610714,
l_shipment_nums,
l_estimated_pickup_dates,
l_ship_methods */
FROM DUAL;
select instr(x_supply_number, '(')
into x_pare_left
from dual;
select instr(x_supply_number, ')')
into x_pare_right
from dual;
select to_number(substr(x_supply_number, x_pare_left+1,
x_pare_right - x_pare_left-1))
into x_release_num
from dual;
select po_release_id,release_type
into l_po_release_id,l_release_type
from po_releases_all
where po_header_id = x_po_header_id
and release_num = x_release_num;
SELECT org_id
INTO l_document_org_id
FROM po_releases_all
WHERE po_release_id = l_po_release_id;/*Bug 5255550*/
SELECT org_id
INTO l_document_org_id
FROM po_headers_all
WHERE po_header_id = x_po_header_id;
Hence for performance reasons, we will use scratch pad, to insert data
and then use bulk collect to get all the data in plsql table*/
/* If l_po_release_id is present, it means that the document is a release */
l_session_gt_key := PO_CORE_S.get_session_gt_nextval();
insert into po_session_gt (key,
num1,
num2,
num3, --bug9693292
date1,
date2,
char1, -- we're using this for date passed in.
char2 -- we're using this to mark a valid record
)
select l_session_gt_key,
pll.line_location_id,
pll.shipment_num,
X_po_line_ids(i), --bug9693292
-- Have picked this from the existing code. From the planning perspective
-- promised date becomes important and should be preferred over nbd
Nvl(pll.promised_date,pll.need_by_date),
X_need_by_dates(i),
To_Char(X_need_by_dates_old(i),'DD-MON-YYYY:hh:mi:ss'),
'Y'
from po_line_locations_all pll
where pll.po_line_id = X_po_line_ids(i)
and pll.po_header_id = X_po_header_id
and (l_po_release_id is NULL OR
pll.po_release_id = l_po_release_id);
insert into po_session_gt (key,
num1,
num2,
num3, --bug9693292
date1,
date2,
char1, -- we're using this for date passed in.
char2 -- we're using this to mark a valid record
)
select l_session_gt_key,
pll.line_location_id,
X_shipment_nums(i),
X_po_line_ids(i), --bug9693292
-- Have picked this from the existing code. From the planning perspective
-- promised date becomes important and should be preferred over nbd
Nvl(pll.promised_date,pll.need_by_date),
X_need_by_dates(i),
To_Char(X_need_by_dates_old(i),'DD-MON-YYYY:hh:mi:ss'),
'Y'
from po_line_locations_all pll
where pll.po_line_id = X_po_line_ids(i)
and pll.shipment_num = Nvl(X_shipment_nums(i), pll.shipment_num)
and pll.po_header_id = X_po_header_id
and (l_po_release_id is NULL OR
pll.po_release_id = l_po_release_id);
UPDATE po_session_gt scratch
SET num4 = (SELECT line_num FROM po_lines_all WHERE po_line_id = scratch.num3);
UPDATE po_session_gt scratch
SET char2 = 'N'
WHERE scratch.char1 <>
To_char(scratch.date1,'DD-MON-YYYY:hh:mi:ss')
AND scratch.KEY = l_session_gt_key;
SELECT x_supply_number,scratch.num4,scratch.num2,scratch.char1, To_char(scratch.date1,'DD-MON-YYYY:hh:mi:ss')
BULK collect INTO supply_number_tbl,line_number_tbl,shipment_number_tbl,ascp_date_tbl,po_date_tbl
FROM po_session_gt scratch
WHERE scratch.KEY = l_session_gt_key
AND Nvl(scratch.char2,'Y') = 'N';
supply_number_tbl.DELETE;
line_number_tbl.DELETE;
shipment_number_tbl.DELETE;
UPDATE po_session_gt scratch
SET char2 = 'N'
WHERE scratch.date2 IS NULL
AND scratch.KEY = l_session_gt_key;
SELECT x_supply_number,scratch.num4,scratch.num2
BULK collect INTO supply_number_tbl,line_number_tbl,shipment_number_tbl
FROM po_session_gt scratch
WHERE scratch.KEY = l_session_gt_key
AND scratch.date2 IS NULL;
select num1, date2
bulk collect into l_line_location_id_tbl, l_need_by_date_tbl
from po_session_gt SCRATCH
WHERE KEY = l_session_gt_key
AND Nvl(scratch.char2,'Y') = 'Y' ;
select date2
bulk collect into l_promised_by_date_tbl
from po_session_gt SCRATCH
WHERE KEY = l_session_gt_key
AND Nvl(scratch.char2,'Y') = 'Y' ;
/*Step 7: Call update_documents; */
PO_DOCUMENT_UPDATE_PVT.update_document(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
x_return_status => l_return_status,
p_changes => l_changes,
p_run_submission_checks => FND_API.G_FALSE,
p_launch_approvals_flag => FND_API.G_TRUE,
p_buyer_id => NULL,
p_update_source => NULL,
p_override_date => NULL,
x_api_errors => l_api_errors
);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'After return from update document');
/* Delete all the data from po_session_gt */
l_progress:= '170';
DELETE FROM po_session_gt
WHERE KEY = l_session_gt_key;
DELETE FROM po_session_gt
WHERE KEY = l_session_gt_key;
DELETE FROM po_session_gt
WHERE KEY = l_session_gt_key;
UPDATE po_requisition_lines_all REQ
SET REQ.estimated_pickup_date =
NVL(p_estimated_pickup_dates(i), REQ.estimated_pickup_date),
REQ.ship_method = NVL(p_ship_methods(i), REQ.ship_method)
WHERE REQ.line_location_id = p_line_location_id_tbl(i);
p_message => 'Can not update estimated_pickup_date and ship_method on requisitions');