[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT pl.po_line_id,
pl.line_num,
ph.start_date,
ph.end_date,
pl.item_id,
ph.vendor_id,
ph.vendor_site_id,
DECODE(ph.approved_flag,'Y','APPROVED',null)
FROM po_lines_all pl,
po_headers_all ph
WHERE pl.po_header_id = ph.po_header_id
AND ph.po_header_id = x_header_id
AND pl.item_id is not null
AND nvl(pl.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(pl.cancel_flag,'N') = 'N'
AND NOT EXISTS
(SELECT 'Line included in Prior Revision'
FROM po_lines_archive pla
WHERE pla.po_line_id = pl.po_line_id
AND pla.revision_num < ph.revision_num)
AND pl.line_num > x_prev_line_num
ORDER BY ph.po_header_id, pl.line_num;
SELECT pl.po_line_id,
pl.line_num,
ph.start_date,
ph.end_date,
pl.item_id,
ph.vendor_id,
ph.vendor_site_id,
DECODE(ph.approved_flag,'Y','APPROVED',null)
FROM po_lines_all pl,
po_headers_all ph
WHERE pl.po_header_id = ph.po_header_id
AND ph.po_header_id = x_header_id
AND pl.item_id is not null
AND nvl(pl.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(pl.cancel_flag,'N') = 'N'
AND pl.line_num > x_prev_line_num
ORDER BY ph.po_header_id, pl.line_num;
select vendor_site_id
from po_ga_org_assignments
where po_header_id = v_doc_id
and enabled_flag = 'Y';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select global_agreement_flag,vendor_site_id
into l_ga_flag , l_vendor_site_id
from po_headers_all
where po_header_id = l_document_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
l_create_update_flag VARCHAR2(20);
l_update_sourcing_rule_flag varchar2(1);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
l_update_sourcing_rule_flag := wf_engine.GetItemAttrText(itemtype, itemkey, 'UPDATE_SOURCING_RULE');
l_create_update_flag := 'CREATE';
if l_update_sourcing_rule_flag = 'Y' then
l_create_update_flag := 'CREATE_UPDATE';
PO_SOURCING_RULES_SV.create_update_sourcing_rule(
p_interface_header_id => l_interface_header_id,
p_interface_line_id => l_interface_line_id,
p_item_id => l_item_id,
p_vendor_id => l_vendor_id,
p_po_header_id => l_document_id,
p_po_line_id => l_po_line_id,
p_document_type => l_document_type,
p_approval_status => l_approved_flag,
p_rule_name =>l_rule_name,
p_rule_name_prefix =>l_rule_prefix,
p_start_date =>l_start_date,
p_end_date =>l_end_date,
p_create_update_code => l_create_update_flag,
p_organization_id => l_sr_organization_id,
p_assignment_type_id => l_assignment_type_id,
p_po_interface_error_code =>'ASR',
x_header_processable_flag => l_header_processable_flag,
x_return_status =>l_return_status);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
p_update_sourcing_rule IN VARCHAR2,
p_agreement_lines_selection IN VARCHAR2,
p_sourcing_level IN VARCHAR2,
p_inv_org IN HR_ALL_ORGANIZATION_UNITS.organization_id%type,
p_sourcing_rule_name IN VARCHAR2,
p_release_gen_method IN PO_ASL_ATTRIBUTES.release_generation_method%type,
p_assignment_set_id IN MRP_ASSIGNMENT_SETS.assignment_set_id%type) IS
/* Local variables declared for Concurrent Program specific requirements */
l_archive_on_approve PO_DOCUMENT_TYPES_ALL.archive_external_revision_code%type;
l_create_update_flag VARCHAR2(20);
SELECT pl.po_line_id,
-- pl.line_num,
ph.start_date,
ph.end_date,
pl.item_id,
DECODE(ph.approved_flag,'Y','APPROVED',null)
FROM po_lines_all pl,
po_headers_all ph
WHERE pl.po_header_id = ph.po_header_id
AND ph.po_header_id = p_document_id
AND pl.item_id is not null
AND nvl(pl.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(pl.cancel_flag,'N') = 'N'
/* AND NOT EXISTS
(SELECT 'Line included in Prior Revision'
FROM po_lines_archive pla
WHERE pla.po_line_id = pl.po_line_id
AND pla.revision_num < ph.revision_num)*/
AND pl.line_num > l_previous_line_num
AND NVL(pl.expiration_date ,sysdate+1) >= sysdate ---Bug 10022351,10192008
ORDER BY ph.po_header_id, pl.line_num;
SELECT pl.po_line_id,
-- pl.line_num,
ph.start_date,
ph.end_date,
pl.item_id,
DECODE(ph.approved_flag,'Y','APPROVED',null)
FROM po_lines_all pl,
po_headers_all ph
WHERE pl.po_header_id = ph.po_header_id
AND ph.po_header_id = p_document_id
AND pl.item_id is not null
AND nvl(pl.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(pl.cancel_flag,'N') = 'N'
AND NVL(pl.expiration_date ,sysdate+1) >= sysdate ---Bug 10022351,10192008
ORDER BY ph.po_header_id, pl.line_num;
PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_update_sourcing_rule : '||p_update_sourcing_rule);
PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_agreement_lines_selection : '||p_agreement_lines_selection);
SELECT archive_external_revision_code
INTO l_archive_on_approve
FROM po_document_types_all
WHERE org_id=l_orgid
AND document_type_code='PA'
AND document_subtype='BLANKET';
IF(p_agreement_lines_selection='NEW')THEN
IF g_po_pdoi_write_to_file ='Y' THEN
l_progress :='005';
PO_DEBUG.put_line(l_progress||' : Agreement Lines Selection :'||p_agreement_lines_selection);
SELECT max(pola.line_num)
INTO l_previous_line_num
FROM
po_lines_archive_all pola,
po_headers_all poh
WHERE
poh.po_header_id=p_document_id
AND pola.po_header_id=poh.po_header_id
AND pola.revision_num < poh.revision_num ;
PO_DEBUG.put_line(l_progress||': Previous Line Number :'||p_agreement_lines_selection);
IF p_agreement_lines_selection='NEW' THEN
OPEN get_new_lines_to_be_processed;
l_create_update_flag := 'CREATE';
IF p_update_sourcing_rule = 'Y' THEN
l_create_update_flag := 'CREATE_UPDATE';
IF p_agreement_lines_selection='NEW' THEN
FETCH get_new_lines_to_be_processed
INTO l_po_line_id, l_start_date, l_end_date,
l_item_id, l_approved_flag;
p_create_update_code => l_create_update_flag,
p_interface_error_code => 'PO_DOCS_OPEN_INTERFACE',
x_header_processable_flag => l_header_processable_flag
);
IF p_agreement_lines_selection='NEW' THEN
CLOSE get_new_lines_to_be_processed;
IF p_agreement_lines_selection='NEW' and get_new_lines_to_be_processed%isopen THEN
CLOSE get_new_lines_to_be_processed;
p_create_update_code IN VARCHAR2,
p_interface_error_code IN VARCHAR2,
x_header_processable_flag IN OUT NOCOPY VARCHAR2
)IS
l_document_id PO_HEADERS_ALL.po_header_id%type;
select vendor_site_id
from po_ga_org_assignments
where po_header_id = p_document_id
and enabled_flag = 'Y';
PO_DEBUG.put_line(l_progress||'Calling procedure create_update_sourcing_rules ');
PO_SOURCING_RULES_SV.create_update_sourcing_rule(
p_interface_header_id => p_interface_header_id,
p_interface_line_id => p_interface_line_id,
p_item_id => p_item_id,
p_vendor_id => p_vendor_id,
p_po_header_id => p_document_id,
p_po_line_id => p_po_line_id,
p_document_type => p_document_type,
p_approval_status => p_approval_status,
p_rule_name => p_rule_name,
p_rule_name_prefix => p_rule_name_prefix,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_create_update_code => p_create_update_code,
p_organization_id => p_inv_org_id,
p_assignment_type_id => l_assignment_type_id,
p_po_interface_error_code => p_interface_error_code,
x_header_processable_flag => x_header_processable_flag,
x_return_status => x_return_status,
--
p_assignment_set_id => p_assignment_set_id,
p_vendor_site_id => p_vendor_site_id);
SELECT global_agreement_flag,vendor_site_id
INTO l_ga_flag , l_vendor_site_id
FROM po_headers_all
WHERE po_header_id = p_document_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select vendor_id, vendor_site_id
into l_vendor_id, l_vendor_site_id
from po_headers_all
where po_header_id = l_document_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
,'ALL' -- select agreement lines
,null -- assignment set
,null -- sourcing level
,null -- Inv org enable
,null -- inventory organization
,null -- sourcing rule name
,l_release_generation_method -- Release generation method
);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);