[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT need_by_date,
ship_to_location_id,
ship_to_organization_id,
consigned_flag
FROM po_line_locations_merge_v
WHERE po_line_id = p_po_line_id_to_compare
AND draft_id = p_draft_id; --Autocreate grouping
SELECT 'ADD'
INTO l_line_action_tbl(i)
FROM po_lines_merge_v
WHERE po_header_id = p_add_to_po_header_id
AND line_num = p_po_line_number_tbl(i)
AND nvl(draft_id,-1) = nvl(p_draft_id,-1);
SELECT index_num1 -- requisition line ID
FROM po_session_gt
WHERE key = c_key
AND index_num2 = c_po_line_num
AND index_num1 <> c_current_req_line_id;
INSERT INTO po_session_gt(
key, -- unique key
index_num1, -- req line ID
index_num2 -- PO line num
)
VALUES (l_key, p_req_line_id_tbl(i), p_po_line_number_tbl(i));
SELECT progress_payment_flag
INTO l_progress_payment_flag
FROM po_doc_style_headers
WHERE style_id = p_style_id;
SELECT NVL(
(SELECT 'Y'
FROM dual
WHERE EXISTS(
-- Select all doc builder requisition lines that have the same PO line
-- number as the current line in the loop.
SELECT 'doc builder lines with same PO line number'
FROM po_session_gt POSGT2
WHERE POSGT2.index_num1 <> POSGT.index_num1 -- Not the current line
AND POSGT2.index_num2 = POSGT.index_num2 -- Same PO line number
)
OR EXISTS(
-- Select all PO lines that have the same PO line number as the
-- current line in the loop.
SELECT 'PO lines with same PO line number'
FROM po_lines_all
WHERE po_header_id = p_add_to_po_header_id
AND line_num = POSGT.index_num2 -- Same PO line number
)),
'N' -- NVL to 'N' if no other req/PO lines with same PO line number
)
BULK COLLECT INTO l_line_combined_flag_tbl
FROM po_session_gt POSGT
WHERE key = l_key;
SELECT po_line_id
INTO l_po_line_id_to_compare
FROM po_lines_merge_v
WHERE po_header_id = p_add_to_po_header_id
AND draft_id = p_draft_id
AND line_num = l_po_line_num;
DELETE FROM po_session_gt
WHERE key = l_key;
DELETE FROM po_session_gt
WHERE key = l_key;
SELECT paha.contract_type,pon_auction_pkg.get_message_suffix(doc.internal_name)
INTO sol_contract_type,message_suffix
FROM pon_auction_headers_all paha, pon_auc_doctypes doc
WHERE paha.auction_header_id =p_draft_id
and paha.doctype_id = doc.doctype_id;
SELECT prl.line_type_id, nvl(prl.item_id, -1), nvl(prl.item_revision, -1), nvl(prl.category_id, -1)
, nvl(pol.ip_category_id, -1)
,nvl(decode(plt.order_type_lookup_code, 'AMOUNT', '1', mom.uom_code), 'NULL')
,prl.group_line_id,
NVL((SELECT nvl(ship_to_location_id,location_id) FROM hr_locations WHERE location_id = prl.deliver_to_location_id), -1)
INTO req_line_type_id, req_item_id, req_item_revision, req_category_id
,req_line_ip_category_id
,req_line_uom
,req_group_line_id
,req_ship_to_location_id
FROM po_requisition_lines_all prl,po_line_types_b plt, mtl_units_of_measure mom, po_lines_all pol
WHERE prl.requisition_line_id = l_req_line_id
AND prl.line_type_id = plt.line_type_id
AND mom.unit_of_measure (+) = prl.unit_meas_lookup_code
AND pol.po_header_id(+) = prl.blanket_po_header_id AND pol.line_num(+) = prl.blanket_po_line_num;
SELECT paip.line_type_id, nvl(paip.item_id, -1), nvl(paip.item_revision, -1), nvl(paip.category_id, -1), paip.group_line_id,
Nvl(paip.clm_info_flag , 'N'),
nvl(paip.clm_option_indicator, 'B')
,nvl(paip.ip_category_id, -1)
,paip.uom_code
,paip.ship_to_location_id
,paip.line_origination_code
INTO sol_line_type_id, sol_item_id, sol_item_revision, sol_category_id, sol_group_line_id,
sol_clm_info_flag,sol_clm_option_indicator
,sol_ip_category_id
,sol_uom_code
,sol_ship_to_location_id
,sol_line_orig_code
FROM pon_auction_item_prices_all paip
WHERE paip.line_number =l_sol_line_num
AND paip.auction_header_id =p_draft_id;
SELECT paip.line_type_id, nvl(paip.item_id, -1), nvl(paip.item_revision, -1), nvl(paip.category_id, -1), paip.group_line_id,
Nvl(paip.clm_info_flag , 'N'),
nvl(paip.clm_option_indicator, 'B')
,nvl(paip.ip_category_id, -1)
,paip.uom_code
,paip.ship_to_location_id
,paip.line_origination_code
INTO sol_line_type_id, sol_item_id, sol_item_revision, sol_category_id, sol_group_line_id,
sol_clm_info_flag,sol_clm_option_indicator
,sol_ip_category_id
,sol_uom_code
,sol_ship_to_location_id
,sol_line_orig_code
FROM pon_auction_item_prices_all paip
WHERE paip.line_num_display =l_sol_line_num_disp
AND paip.auction_header_id =p_draft_id;
This matching is done only when the action selected is 'ADD' and not 'NEW'
2)If the action is 'ADD' then find_matching_po_line_num function is called. No change needs to be done here too.
3)If the action is 'NEW' then clm_find_matching_builder_line_num is called. this is a new function based on the existing find_matching_builder_line_num.
This will find the matching req lines that are selected and are in the interface.
We call this only if the present requisition line that is being checked for match
+ is not a SLIN
+ is not a info line
+ is not a option line
This condition will take care of the following grouping rules mentioned in the FDD,
? Priced CLIN Line without SLIN Can Be Grouped If the Grouping Criteria Match
? Single Priced CLIN with Info SLINs Holding the Funds Can Be Grouped If the Grouping Criteria Match
? Info CLIN with Priced SLINs Are Not Grouped
? Option Lines Are Not Grouped
We only group the CLINs and add the SLINs to the grouped CLIN and options are alse not grouped. So we will look for matching lines only for CLINs
*/
Procedure clm_group_by_default
(
p_req_line_id_tbl IN PO_TBL_NUMBER
, p_req_group_line_id_tbl IN PO_TBL_NUMBER
, p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
, p_req_option_flag_tbl IN PO_TBL_VARCHAR1
, p_po_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
, p_consigned_flag_tbl IN PO_TBL_VARCHAR1
, p_add_to_po_header_id IN NUMBER
, p_draft_id IN NUMBER --Autocreate grouping
, p_builder_agreement_id IN NUMBER
, p_start_index IN NUMBER
, p_end_index IN NUMBER
, p_po_line_num_tbl IN OUT NOCOPY PO_TBL_NUMBER
)
IS
l_max_line_num NUMBER;
This matching is done only when the action selected is 'ADD' and not 'NEW'
2)If the action is 'ADD' then find_matching_po_line_num function is called. No change needs to be done here too.
3)If the action is 'NEW' then clm_find_matching_builder_line_num is called. this is a new function based on the existing find_matching_builder_line_num.
This will find the matching req lines that are selected and are in the interface.
We call this only if the present requisition line that is being checked for match
+ is not a SLIN
+ is not a info line
+ is not a option line
This condition will take care of the following grouping rules mentioned in the FDD,
? Priced CLIN Line without SLIN Can Be Grouped If the Grouping Criteria Match
? Single Priced CLIN with Info SLINs Holding the Funds Can Be Grouped If the Grouping Criteria Match
? Info CLIN with Priced SLINs Are Not Grouped
? Option Lines Are Not Grouped
We only group the CLINs and add the SLINs to the grouped CLIN and options are alse not grouped. So we will look for matching lines only for CLINs
*/
Procedure clm_group_sol_by_default
(
p_req_line_id_tbl IN PO_TBL_NUMBER
, p_req_group_line_id_tbl IN PO_TBL_NUMBER
, p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
, p_req_option_flag_tbl IN PO_TBL_VARCHAR1
, p_neg_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
, p_consigned_flag_tbl IN PO_TBL_VARCHAR1
, p_add_to_neg_header_id IN NUMBER
, p_draft_id IN NUMBER --Autocreate grouping
, p_builder_agreement_id IN NUMBER
, p_start_index IN NUMBER
, p_end_index IN NUMBER
, p_neg_line_num_tbl IN OUT NOCOPY PO_TBL_NUMBER
)
IS
l_max_line_num NUMBER;
SELECT Max(LINE_NUMBER) INTO l_max_line_num FROM pon_auction_item_prices_all WHERE AUCTION_HEADER_ID = p_draft_id;
SELECT PRL.item_id,
PRL.need_by_date,
PRL.destination_organization_id,
PRL.deliver_to_location_id
INTO l_item_id,
l_req_line_delivery_info.need_by_date,
l_req_line_delivery_info.ship_to_organization_id,
l_req_line_delivery_info.ship_to_location_id
FROM po_requisition_lines_all PRL
WHERE PRL.requisition_line_id = p_req_line_id;
SELECT PRL.item_id,
PRL.item_description,
PRL.item_revision,
PRL.order_type_lookup_code,
PRL.purchase_basis,
PRL.matching_basis,
PRL.preferred_grade,
PRL.unit_meas_lookup_code,
PRL.transaction_reason_code,
DECODE(
PRL.document_type_code,
'CONTRACT',
PRL.blanket_po_header_id,
NULL
), -- contract ID
DECODE(
PRL.document_type_code,
'CONTRACT',
NULL,
PRL.blanket_po_header_id
), -- source document ID
DECODE(
PRL.document_type_code,
'CONTRACT',
NULL,
SRC_DOC_LINE.po_line_id
), -- source document line ID
NULL, -- cancel flag N/A for req line
NULL, -- closed code N/A for req line
PRL.supplier_ref_number
, PRL.GROUP_LINE_ID
, PRL.CLM_INFO_FLAG
, PRL.CLM_BASE_LINE_NUM
, PRL.category_id --bugfix#16097884
INTO l_req_line_info
FROM po_requisition_lines_all PRL,
po_lines_all SRC_DOC_LINE
WHERE PRL.requisition_line_id = p_req_line_id
AND SRC_DOC_LINE.po_header_id(+) = PRL.blanket_po_header_id
AND SRC_DOC_LINE.line_num(+) = PRL.blanket_po_line_num;
SELECT item_id,
item_description,
item_revision,
order_type_lookup_code,
purchase_basis,
matching_basis,
preferred_grade,
unit_meas_lookup_code,
transaction_reason_code,
contract_id,
from_header_id, -- source document ID
from_line_id, -- source document line ID
cancel_flag,
closed_code,
supplier_ref_number
,GROUP_LINE_ID
,CLM_INFO_FLAG
,CLM_BASE_LINE_NUM
,category_id --bugfix#16097884
INTO l_po_line_info
FROM po_lines_merge_v /* changing to po_lines_merge_v and passing in the draft_id, for the 'add to mods' flow*/
WHERE po_line_id = p_po_line_id
AND draft_id = p_draft_id;
SELECT requisition_header_id
INTO l_req_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id_tbl(i);
INSERT INTO po_session_gt
( key
, num1
, num2
)
SELECT DISTINCT
l_key
, prl.requisition_line_id
, pol.line_num
FROM po_requisition_lines_all prl
, po_lines_merge_v pol
, po_line_locations_merge_v pll
, po_lines_merge_v src_line
WHERE pol.po_header_id = p_add_to_po_header_id
AND pol.draft_id = p_draft_id
AND pll.po_line_id = pol.po_line_id
AND pll.draft_id = pol.draft_id
AND prl.requisition_line_id = p_req_line_id_tbl(i)
AND prl.group_line_id IS NULL
AND prl.clm_base_line_num IS NULL
AND Nvl(prl.clm_info_flag ,'N')='N'
AND pol.group_line_id IS NULL
AND pol.clm_base_line_num IS NULL
AND Nvl(pol.clm_info_flag ,'N')='N'
AND decode ( prl.item_id
, pol.item_id, 1, 0) = 1
AND ((prl.item_id IS NOT NULL OR pol.item_id IS NOT NULL)
OR decode(
prl.item_description,
pol.item_description, 1, 0) = 1)
AND decode ( prl.item_revision
, pol.item_revision, 1, 0) = 1
AND decode ( prl.line_type_id
, pol.line_type_id, 1, 0) = 1
AND decode ( prl.preferred_grade
, pol.preferred_grade, 1, 0) = 1
AND decode ( prl.unit_meas_lookup_code
, pol.unit_meas_lookup_code, 1, 0) = 1
AND decode ( prl.transaction_reason_code
, pol.transaction_reason_code, 1, 0) = 1
AND decode ( prl.supplier_ref_number
, pol.supplier_ref_number, 1, 0) = 1
AND ( ( l_need_by_grouping_profile = 'N' )
OR ( decode ( trunc(prl.need_by_date,'MI')
, trunc(pll.need_by_date,'MI'), 1, 0) = 1 ) )
AND ( ( l_ship_to_grouping_profile = 'N' )
OR ( decode ( prl.destination_organization_id
, pll.ship_to_organization_id, 1, 0) = 1 ) )
AND ( ( prl.document_type_code <> 'CONTRACT' )
OR ( decode ( prl.blanket_po_header_id
, pol.contract_id, 1, 0) = 1 ) )
AND src_line.po_header_id (+) = prl.blanket_po_header_id
AND src_line.line_num (+) = prl.blanket_po_line_num
AND ( ( p_builder_agreement_id IS NOT NULL )
OR ( ( decode ( prl.blanket_po_header_id
, pol.from_header_id, 1, 0) = 1 )
AND ( decode ( src_line.po_line_id
, pol.from_line_id, 1, 0) = 1 ) ) )
AND nvl(pol.cancel_flag, 'N') <> 'Y'
AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND nvl(pll.consigned_flag, 'N') = p_consigned_flag_tbl(i);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num2
BULK COLLECT INTO x_req_line_id_tbl, x_po_line_num_tbl;
INSERT INTO po_session_gt
( key
, num1
, num2
, char1
)
SELECT DISTINCT
l_key
, prl.requisition_line_id
, pol.line_num
, pol.line_num_display
FROM po_requisition_lines_all prl
, po_lines_merge_v pol
, po_line_locations_merge_v pll
, po_lines_merge_v src_line
WHERE pol.po_header_id = p_add_to_po_header_id
AND nvl(pol.draft_id,-1) = nvl(p_draft_id,-1)
AND pll.po_line_id = pol.po_line_id
AND nvl(pll.draft_id,-1) = nvl(pol.draft_id,-1)
AND prl.requisition_line_id = p_req_line_id_tbl(i)
AND prl.group_line_id IS NULL
AND prl.clm_base_line_num IS NULL
AND Nvl(prl.clm_info_flag ,'N')='N'
AND pol.group_line_id IS NULL
AND pol.clm_base_line_num IS NULL
AND Nvl(pol.clm_info_flag ,'N')='N'
AND decode ( Nvl(prl.item_id,-1)
, Nvl(pol.item_id,-1), 1, 0) = 1
AND ((prl.item_id IS NOT NULL OR pol.item_id IS NOT NULL)
OR decode(
prl.item_description,
pol.item_description, 1, 0) = 1)
AND decode ( prl.item_revision
, pol.item_revision, 1, 0) = 1
AND decode ( prl.line_type_id
, pol.line_type_id, 1, 0) = 1
AND decode ( prl.preferred_grade
, pol.preferred_grade, 1, 0) = 1
AND decode ( prl.unit_meas_lookup_code
, pol.unit_meas_lookup_code, 1, 0) = 1
AND decode ( prl.transaction_reason_code
, pol.transaction_reason_code, 1, 0) = 1
AND decode ( prl.supplier_ref_number
, pol.supplier_ref_number, 1, 0) = 1
AND ( ( l_need_by_grouping_profile = 'N' )
OR ( decode ( trunc(prl.need_by_date,'MI')
, trunc(pll.need_by_date,'MI'), 1, 0) = 1 ) )
AND ( ( l_ship_to_grouping_profile = 'N' )
OR ( decode ( prl.destination_organization_id
, pll.ship_to_organization_id, 1, 0) = 1 ) )
AND ( ( prl.document_type_code <> 'CONTRACT' )
OR ( decode ( prl.blanket_po_header_id
, pol.contract_id, 1, 0) = 1 ) )
AND src_line.po_header_id (+) = prl.blanket_po_header_id
AND src_line.line_num (+) = prl.blanket_po_line_num
AND ( ( p_builder_agreement_id IS NOT NULL )
OR ( ( decode ( prl.blanket_po_header_id
, pol.from_header_id, 1, 0) = 1 )
AND ( decode ( src_line.po_line_id
, pol.from_line_id, 1, 0) = 1 ) ) )
AND nvl(pol.cancel_flag, 'N') <> 'Y'
AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND nvl(pll.consigned_flag, 'N') = p_consigned_flag_tbl(i);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num2, char1
BULK COLLECT INTO x_req_line_id_tbl, x_po_line_num_tbl, x_po_line_num_disp_tbl;
SELECT nvl(max(line_num), 0)
INTO x_max_po_line_num
FROM po_lines_merge_v
WHERE po_header_id = p_po_header_id;
SELECT nvl(max(line_num_display), '0000')
INTO x_max_po_line_num
FROM po_lines_merge_v
WHERE po_header_id = p_po_header_id
--AND draft_id = p_draft_id
and group_line_id is null
and CLM_EXHIBIT_NAME IS null; --Bug 16572476
In case of PAR ELINs, when the ELIN line is selected in DWB.
It will be considered to find out the max clin number. It should
not be considered. So skipping ELIN lines by finding out if the line number
is having characters or not. ELIN Numbers will have characters
*/
IF ((TRIM(TRANSLATE(p_po_line_num_tbl(i),'+-.0123456789',' ')) IS NULL) -- Check whether it is a number or char
AND p_po_line_num_tbl(i) > x_max_po_line_num and p_req_group_line_id_tbl(i) is null)
THEN
x_max_po_line_num := p_po_line_num_tbl(i);
SELECT nvl(max(line_num_display), '0000')
INTO x_max_po_line_num
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_draft_id
and group_line_id is null;
SELECT item_id
INTO l_item_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id_tbl(i);
SELECT 'Y' INTO l_is_info_fund_req_line
FROM po_requisition_lines_all PRL
WHERE
prl.REQUISITION_LINE_ID = p_req_line_id and
nvl(prl.clm_info_flag, 'N') = 'N' AND prl.group_line_id IS NULL AND EXISTS (SELECT 1
FROM po_req_distributions_all prd1 WHERE prd1.requisition_line_id = prl.REQUISITION_LINE_ID
AND prd1.info_line_id IS NOT NULL AND ROWNUM = 1);
SELECT po_line_id, Decode(clm_option_indicator, 'O', 'Y', 'N')
INTO l_po_line_id, l_is_option_line
FROM po_lines_merge_v
WHERE po_header_id = p_po_header_id AND
line_num = p_po_line_num AND
draft_id <> -1;
SELECT Max(line_num_display)
INTO l_max_slin_num
FROM po_lines_merge_v
WHERE group_line_id = l_po_line_id AND
draft_id <> -1 AND
Nvl(clm_info_flag, 'N') = 'Y' AND
group_line_id IS NOT NULL;
SELECT 'Y'
INTO l_lock_available
FROM dual
WHERE NOT EXISTS (SELECT 'Another Mod has F lock'
FROM po_entity_locks poel,
po_lines_merge_v plm
WHERE plm.po_line_id = poel.entity_pk1
AND plm.po_header_id = p_add_to_po_header_id
AND plm.draft_id = p_draft_id
AND plm.line_num = p_po_line_number_tbl(i)
AND poel.entity_name = 'PO_LINE'
AND poel.lock_by_draft_id <> p_draft_id
AND poel.lock_type = 'F');
select PO_SESSION_GT_S.nextval into l_session_key from dual;
index_num1 - index, this is inserted to ensure correct order of retrieval
*/
-- now do the bulk insert
-- When the line_num_display are edited on the details page
IF p_orig_line_num <> -1 AND p_edit_line_num <> -1
THEN
-- Renumber flag needs to be set 'Y' only for po_line_num which match
-- p_orig_line_num OR p_edit_line_num
FOR i IN l_start_index..l_end_index LOOP
IF p_po_line_num_tbl(i) = p_orig_line_num OR p_po_line_num_tbl(i) = p_edit_line_num THEN
l_renumber_flag(i) := 'Y';
insert into po_session_gt (key, num1, num2, num3, char1, char2, char3, char5, index_num1)
values ( l_session_key,
p_source_line_id_tbl(i)
, p_source_group_line_id_tbl(i)
, p_po_line_num_tbl(i)
, p_po_line_num_disp_tbl(i)
, l_action_tbl(i)
, l_renumber_flag(i)
, nvl(p_req_clm_info_flag_tbl(i), 'N')
, i
);
insert into po_session_gt (key, num1, num2, num3, char1, char2, char3, char5, index_num1)
values ( l_session_key,
p_source_line_id_tbl(i)
, p_source_group_line_id_tbl(i)
, p_po_line_num_tbl(i)
, p_po_line_num_disp_tbl(i)
, l_action_tbl(i)
, nvl(p_renumber_flag(i), 'Y')
, nvl(p_req_clm_info_flag_tbl(i), 'N')
, i
);
UPDATE po_session_gt
SET num4 = (select po_line_id
FROM PO_LINES_MERGE_V
where PO_HEADER_ID = p_add_to_po_header_id
AND LINE_NUM_DISPLAY = char1
AND ROWNUM=1)
where num2 is null -- group line id
and char2 = 'ADD' -- action
and char3 = 'Y' -- renumber flag
and index_num1 between l_start_index and l_end_index
and key = l_session_key;
update po_session_gt
set
char4 = PO_LINES_DRAFT_PVT.get_next_slin_num(num4, --po_line_id
NULL,
char1,--line_num_display
'Y', --clmInfoFlag
-9999),--Invalid Draft Id
char6 = PO_LINES_DRAFT_PVT.get_next_slin_num(num4, --po_line_id
NULL,
char1,--line_num_display
'N', --clmInfoFlag
-9999)--Invalid Draft Id
where num2 is null -- group line id
and char2 = 'ADD' -- action
and char3 = 'Y' -- renumber flag
and index_num1 between l_start_index and l_end_index
and key = l_session_key;
update po_session_gt
set char4 = char1 || '01',
char6 = char1 || 'AA'
where num2 is null -- group line id
and char2 = 'NEW' -- action
and char3 = 'Y' -- renumber flag
and index_num1 between l_start_index and l_end_index
and key = l_session_key;
for po_line_num in (select distinct num3, char4, char6 -- char4 is the next slin number, previous logic ensure that a given value of num3 will have same char4
from po_session_gt
where key = l_session_key
and char3 = 'Y' -- renumber flag
and index_num1 between l_start_index and l_end_index
--and char5 = 'N' --priced
and num2 is null -- clin
)
loop
l_next_info_slin_num := po_line_num.char4;
for rec in (select slins.num1, slins.char5 from po_session_gt clins, po_session_gt slins
where clins.key = l_session_key
and slins.key = l_session_key
AND clins.num3 = po_line_num.num3 -- po line number
and slins.num2 = clins.num1 -- slins.group line id = clins.line id
order by slins.index_num1
)
LOOP
IF rec.char5 = 'Y' THEN -- info slin
l_next_slin_num := l_next_info_slin_num;
update po_session_gt
set char1 = l_next_slin_num
where key = l_session_key
and num1 = rec.num1;
select char1
bulk collect into p_po_line_num_disp_tbl
from po_session_gt
where key = l_session_key
order by index_num1; -- order is important
SELECT line_num
INTO l_line_num
FROM po_lines_merge_v
WHERE nvl(draft_id,-1) = nvl(p_draft_id,-1)
--Bug 13552726 : While creating new modification the draft_id was null
--Hence the query returned no value. The line_num was not set and no
--validations were fired.
AND po_header_id = p_add_to_po_header_id
AND line_num_display = p_po_line_num_disp;
New procedure to update the po line num and po line num display based on the shipment selected during the 'ADD_FUNDS' case.*/
-------------------------------------------------------------------------------
--Start of Comments
--Name: get_po_linenum_for_shipment
--Function:
--Derives the po_line_num and po_line_num_display for the shipment num selected in ADD_FUNDS case
--Parameters:
--IN:
--p_po_shipment_id
-- The line_location_id of the shipment to which the funds will be added.
-- builder.
--p_draft_id
-- The draft ID of the mod for the Add funds To PO/Mod case. If Add funds to PO case, this will be -1
--x_po_line_num
-- The po line number of the PO line to which the shipment belongs
--x_po_line_num_disp
--The po line number of the PO line to which the shipment belongs
--End of Comments
-------------------------------------------------------------------------------
PROCEDURE get_po_linenum_for_shipment
(
p_po_shipment_id IN NUMBER
, p_draft_id IN NUMBER
, x_po_line_num OUT NOCOPY NUMBER
, x_po_line_num_disp OUT NOCOPY VARCHAR2
)
IS
d_mod CONSTANT VARCHAR2(100) := D_get_po_linenum_for_shipment ;
SELECT line_num, line_num_display
INTO x_po_line_num, x_po_line_num_disp
FROM po_lines_merge_v plm,
po_line_locations_merge_v pllm
WHERE plm.po_line_id = pllm.po_line_id
AND plm.draft_id = pllm.draft_id
AND pllm.line_location_id = p_po_shipment_id
AND pllm.draft_id = p_draft_id;
SELECT item_id, item_description
INTO l_po_item_id, l_po_item_desc
FROM po_lines_all
WHERE po_header_id = p_po_header_id
AND line_num = p_po_line_num;