nvl(to_char(l_new_need_by_date), ' ') || '
' || NL;
[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(sum(decode(pcr1.action_type, 'CANCELLATION', 0, nvl(pcr1.new_price, prl.unit_price)*
nvl(pcr2.new_quantity,prd.req_line_quantity)*prd.nonrecoverable_tax
/(prl.unit_price*prd.req_line_quantity))), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
into l_new_tax_amount
from po_requisition_lines_all prl,
po_req_distributions_all prd,
po_change_requests pcr1,
po_change_requests pcr2
where prl.requisition_line_id=pcr1.document_line_id(+)
and pcr1.change_request_group_id(+)=l_change_request_group_id
and pcr1.request_level(+)='LINE'
and prl.requisition_line_id=prd.requisition_line_id
and nvl(prd.nonrecoverable_tax, 0) >0
and prd.distribution_id=pcr2.document_distribution_id(+)
and pcr2.change_request_group_id(+)=l_change_request_group_id
and prl.requisition_header_id=l_document_id
AND NVL(prl.modified_by_agent_flag, 'N') = 'N'
and NVL(prl.cancel_flag, 'N')='N';
select to_char(sum(decode(pcr1.action_type, 'CANCELLATION', 0, nvl(pcr1.new_price, prl.unit_price)*
nvl(pcr2.new_quantity,prd.req_line_quantity))),
FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
into l_new_req_amount
from po_requisition_lines_all prl,
po_req_distributions_all prd,
po_change_requests pcr1,
po_change_requests pcr2
where prl.requisition_line_id=pcr1.document_line_id(+)
and pcr1.change_request_group_id(+)=l_change_request_group_id
and pcr1.request_level(+)='LINE'
and prl.requisition_line_id=prd.requisition_line_id
and prd.distribution_id=pcr2.document_distribution_id(+)
and pcr2.change_request_group_id(+)=l_change_request_group_id
and prl.requisition_header_id=l_document_id
AND NVL(prl.modified_by_agent_flag, 'N') = 'N'
and NVL(prl.cancel_flag, 'N')='N';
select wf_item_type, wf_item_key
from po_change_requests
where change_request_group_id=l_change_request_group_id;
SELECT to_char(nvl(sum(nonrecoverable_tax), 0), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
INTO l_new_tax_amount
FROM po_requisition_lines rl,
po_req_distributions rd
WHERE rl.requisition_header_id = l_document_id
AND rd.requisition_line_id = rl.requisition_line_id
AND NVL(rl.modified_by_agent_flag, 'N') = 'N'
and NVL(rl.cancel_flag, 'N')='N';
SELECT to_char(nvl(SUM(quantity * unit_price), 0), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
into l_new_req_amount
FROM po_requisition_lines
WHERE requisition_header_id = l_document_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(modified_by_agent_flag, 'N') = 'N';
SELECT rql.requisition_line_id,
rql.line_num,
msi.concatenated_segments,
rql.item_revision,
rql.item_description,
nvl(muom.unit_of_measure_tl, rql.unit_meas_lookup_code),
rql.quantity,
rql.unit_price,
rql.quantity * rql.unit_price,
rql.need_by_date,
hrt.location_code,
per.full_name,
decode(rql.source_type_code,'VENDOR', rql.suggested_vendor_name, org.organization_code ||' - '||
org.organization_name),
decode(rql.source_type_code, 'VENDOR',rql.suggested_vendor_location,''),
rql.currency_code,
rql.currency_unit_price,
PLC.DISPLAYED_FIELD,
rql.source_type_code,
rql.line_location_id,
rql.cancel_flag
FROM po_requisition_lines rql,
mtl_system_items_kfv msi,
hr_locations_all hrt,
per_all_people_f per,
mtl_units_of_measure muom,
org_organization_definitions org,
PO_LOOKUP_CODES PLC
WHERE rql.requisition_header_id = v_document_id
AND NVL(rql.modified_by_agent_flag, 'N') = 'N'
AND hrt.location_id (+) = rql.deliver_to_location_id
AND rql.item_id = msi.inventory_item_id(+)
AND nvl(msi.organization_id, rql.destination_organization_id) =
rql.destination_organization_id
AND rql.to_person_id = per.person_id(+)
AND per.effective_start_date(+) <= trunc(sysdate)
AND per.effective_end_date(+) >= trunc(sysdate)
AND rql.source_organization_id = org.organization_id (+)
AND muom.unit_of_measure = rql.unit_meas_lookup_code -- bug 2401933.add
AND PLC.LOOKUP_TYPE = 'REQUISITION TYPE'
AND PLC.LOOKUP_CODE = DECODE(RQL.SOURCE_TYPE_CODE,'VENDOR','PURCHASE','INTERNAL')
ORDER BY rql.line_num;
SELECT CODE_COMBINATION_ID
FROM PO_REQ_DISTRIBUTIONS_ALL
WHERE REQUISITION_LINE_ID = req_line_id;
select meaning
into l_cancel_display
from FND_LOOKUPS
where lookup_type='YES_NO'
and lookup_code='Y';
select fs.segment_num, gls.chart_of_accounts_id
into l_segment_num, l_account_id
from FND_ID_FLEX_SEGMENTS fs,
fnd_segment_attribute_values fsav,
financials_system_parameters fsp,
gl_sets_of_books gls
where fsp.set_of_books_id = gls.set_of_books_id and
fsav.id_flex_num = gls.chart_of_accounts_id and
fsav.id_flex_code = 'GL#' and
fsav.application_id = 101 and
fsav.segment_attribute_type = 'FA_COST_CTR' and
fsav.id_flex_num = fs.id_flex_num and
fsav.id_flex_code = fs.id_flex_code and
fsav.application_id = fs.application_id and
fsav.application_column_name = fs.application_column_name and
fsav.attribute_value='Y';
select count(1)
into l_num_lines
from po_requisition_lines
where requisition_header_id = l_document_id;
|| '/OA_MEDIA/newupdateditem_status.gif ALT="">'|| l_req_line_msg;
nvl(to_char(l_new_need_by_date), ' ') || '' || NL;
nvl(to_char(l_new_quantity), ' ')|| '' || NL;
'' || NL;
'' || NL;
'' || NL;
SELECT rql.requisition_line_id,
rql.line_num,
msi.concatenated_segments,
rql.item_revision,
rql.item_description,
nvl(muom.unit_of_measure_tl, rql.unit_meas_lookup_code),
rql.quantity,
rql.unit_price,
rql.quantity * rql.unit_price,
rql.need_by_date,
hrt.location_code,
per.full_name,
decode(rql.source_type_code,'VENDOR', rql.suggested_vendor_name, org.organization_code ||' - '||
org.organization_name),
decode(rql.source_type_code, 'VENDOR',rql.suggested_vendor_location,''),
rql.currency_code,
rql.currency_unit_price,
PLC.DISPLAYED_FIELD,
rql.source_type_code,
rql.line_location_id,
rql.cancel_flag
FROM po_requisition_lines rql,
mtl_system_items_kfv msi,
hr_locations_all hrt,
per_all_people_f per,
mtl_units_of_measure muom,
org_organization_definitions org,
PO_LOOKUP_CODES PLC
WHERE rql.requisition_header_id = v_document_id
AND NVL(rql.modified_by_agent_flag, 'N') = 'N'
AND hrt.location_id (+) = rql.deliver_to_location_id
AND rql.item_id = msi.inventory_item_id(+)
AND nvl(msi.organization_id, rql.destination_organization_id) =
rql.destination_organization_id
AND rql.to_person_id = per.person_id(+)
AND per.effective_start_date(+) <= trunc(sysdate)
AND per.effective_end_date(+) >= trunc(sysdate)
AND rql.source_organization_id = org.organization_id (+)
AND muom.unit_of_measure = rql.unit_meas_lookup_code -- bug 2401933.add
AND PLC.LOOKUP_TYPE = 'REQUISITION TYPE'
AND PLC.LOOKUP_CODE = DECODE(RQL.SOURCE_TYPE_CODE,'VENDOR','PURCHASE','INTERNAL')
ORDER BY rql.line_num;
SELECT CODE_COMBINATION_ID
FROM PO_REQ_DISTRIBUTIONS_ALL
WHERE REQUISITION_LINE_ID = req_line_id;
select wf_item_type, wf_item_key
from po_change_requests
where change_request_group_id=l_group_id;
select meaning
into l_cancel_display
from FND_LOOKUPS
where lookup_type='YES_NO'
and lookup_code='Y';
select fs.segment_num, gls.chart_of_accounts_id
into l_segment_num, l_account_id
from FND_ID_FLEX_SEGMENTS fs,
fnd_segment_attribute_values fsav,
financials_system_parameters fsp,
gl_sets_of_books gls
where fsp.set_of_books_id = gls.set_of_books_id and
fsav.id_flex_num = gls.chart_of_accounts_id and
fsav.id_flex_code = 'GL#' and
fsav.application_id = 101 and
fsav.segment_attribute_type = 'FA_COST_CTR' and
fsav.id_flex_num = fs.id_flex_num and
fsav.id_flex_code = fs.id_flex_code and
fsav.application_id = fs.application_id and
fsav.application_column_name = fs.application_column_name and
fsav.attribute_value='Y';
select count(1)
into l_num_lines
from po_requisition_lines
where requisition_header_id = l_document_id;
l_req_line_msg := ''||''|| l_req_line_msg;
Line: 1485
nvl(to_char(l_new_need_by_date), ' ') || '
' || NL;
nvl(to_char(l_new_quantity), ' ')|| '' || NL;
'' || NL;
'' || NL;
'' || NL;
SELECT poh.SEQUENCE_NUM,
per.FULL_NAME,
polc.DISPLAYED_FIELD,
poh.ACTION_DATE,
poh.NOTE,
poh.OBJECT_REVISION_NUM
from po_action_history poh,
per_people_f per,
po_lookup_codes polc
where OBJECT_TYPE_CODE = v_object_type
and poh.action_code = polc.lookup_code
and POLC.LOOKUP_TYPE IN ('APPROVER ACTIONS','CONTROL ACTIONS')
and per.person_id = poh.employee_id
and trunc(sysdate) between per.effective_start_date
and per.effective_end_date
and OBJECT_ID = v_document_id
UNION ALL
SELECT poh.SEQUENCE_NUM,
per.FULL_NAME,
NULL,
poh.ACTION_DATE,
poh.NOTE,
poh.OBJECT_REVISION_NUM
from po_action_history poh,
per_people_f per
where OBJECT_TYPE_CODE = v_object_type
and poh.action_code is null
and per.person_id = poh.employee_id
and trunc(sysdate) between per.effective_start_date
and per.effective_end_date
and OBJECT_ID = v_document_id
order by 1 desc;
select max(sequence_num)
into l_first_seq
from po_action_history
where action_code='SUBMIT CHANGE'
and object_type_code=l_object_type
and object_id=l_document_id;
SELECT pal.SEQUENCE_NUM,per.FULL_NAME,null,null,null,null
FROM per_people_f per,
po_approval_list_lines pal,
po_approval_list_headers pah
WHERE pah.document_id = v_document_id
and pah.document_type = v_object_type
and pah.latest_revision = 'Y'
and pal.APPROVAL_LIST_HEADER_ID = pah.APPROVAL_LIST_HEADER_ID
and pal.STATUS IS NULL
and per.PERSON_ID = pal.APPROVER_ID
and trunc(sysdate) between per.EFFECTIVE_START_DATE
and per.EFFECTIVE_END_DATE
ORDER BY 1 asc;
select to_char(nvl(sum(nvl(decode(pcr3.action_type, 'CANCELLATION', 0,
decode(prl.unit_price, 0, 0,
nvl(pcr1.new_price, prl.unit_price)*
nvl(pcr2.new_quantity, prl.quantity)*
por_view_reqs_pkg.get_line_nonrec_tax_total(
prl.requisition_line_id)/
(prl.unit_price*prl.quantity))),0)),0),
FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
||' '|| l_currency_code,
to_char(nvl(sum(decode(pcr3.action_type, 'CANCELLATION', 0,
nvl(pcr1.new_price, prl.unit_price)*
nvl(pcr2.new_quantity, prl.quantity))), 0),
FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
||' '|| l_currency_code
into l_new_tax_amount, l_new_req_amount
from po_requisition_lines_all prl,
po_change_requests pcr1,
po_change_requests pcr2,
po_change_requests pcr3
where prl.requisition_line_id=pcr1.document_line_id(+)
and pcr1.change_request_group_id(+)=l_change_request_group_id
and pcr1.request_level(+)='LINE'
and pcr1.change_active_flag(+)='Y'
and pcr1.new_price(+) is not null
and prl.requisition_line_id=pcr2.document_line_id(+)
and pcr2.change_request_group_id(+)=l_change_request_group_id
and pcr2.request_level(+)='LINE'
and pcr2.action_type(+)='DERIVED'
and pcr2.new_quantity(+) is not null
and prl.requisition_line_id=pcr3.document_line_id(+)
and pcr3.change_request_group_id(+)=l_change_request_group_id
and pcr3.request_level(+)='LINE'
and pcr3.action_type(+)='CANCELLATION'
and prl.requisition_header_id=l_document_id
AND NVL(prl.modified_by_agent_flag, 'N') = 'N'
and NVL(prl.cancel_flag, 'N')='N';
SELECT to_char(nvl(sum(nvl(nonrecoverable_tax, 0)), 0),
FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
||' '|| l_currency_code
INTO l_new_tax_amount
FROM po_requisition_lines rl,
po_req_distributions_all rd --
WHERE rl.requisition_header_id = l_document_id
AND rd.requisition_line_id = rl.requisition_line_id
AND NVL(rl.modified_by_agent_flag, 'N') = 'N'
and NVL(rl.cancel_flag, 'N')='N';
SELECT to_char(nvl(SUM(nvl(quantity * unit_price, 0)), 0),
FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
||' '|| l_currency_code
into l_new_req_amount
FROM po_requisition_lines
WHERE requisition_header_id = l_document_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(modified_by_agent_flag, 'N') = 'N';
select currency_code
into l_currency
FROM po_requisition_lines_all
WHERE requisition_header_id = l_document_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(modified_by_agent_flag, 'N') = 'N'
and currency_code <> l_currency_code;
SELECT PH.SEGMENT1|| DECODE(PR.RELEASE_NUM, NULL, '', '-' || PR.RELEASE_NUM)
INTO l_po_num
FROM
PO_RELEASES PR,
PO_HEADERS_ALL PH, --
PO_LINE_LOCATIONS PLL
WHERE
pll.line_location_id=p_line_location_id and
PLL.PO_HEADER_ID = PH.PO_HEADER_ID AND
PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID(+);
select to_char(OOH.ORDER_NUMBER), OOL.FLOW_STATUS_CODE, OOL.LINE_ID
INTO l_so_number, l_status_code, l_line_id
from PO_REQUISITION_LINES PRL,
PO_REQUISITION_HEADERS_ALL PRH, --
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
PO_SYSTEM_PARAMETERS PSP
WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = req_line_id
AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
AND OOL.HEADER_ID = OOH.HEADER_ID
AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
select action_type,
new_price,
old_price,
new_currency_unit_price,
old_currency_unit_price,
new_need_by_date,
old_need_by_date,
request_reason,
request_status,
new_quantity,
old_quantity
from po_change_requests
where change_request_group_id=p_group_id
and document_line_id=p_req_line_id
and request_level='LINE';
select distinct request_status
from po_change_requests
where change_request_group_id=p_group_id
and document_line_id=p_req_line_id
and action_type<>'DERIVED';
select request_reason
from po_change_requests
where change_request_group_id=p_group_id
and document_line_id=p_req_line_id
and request_reason is not null;
select change_request_id
from po_change_requests
where change_request_group_id=p_group_id
and document_line_id=p_req_line_id
and request_level='DISTRIBUTION'
and request_status<>'REJECTED';
select change_request_id
from po_change_requests
where change_request_group_id=p_group_id
and document_line_id=p_req_line_id
and request_level='DISTRIBUTION';
select msi.concatenated_segments
into l_item
from mtl_system_items_kfv msi,
financials_system_params_all fsp
where msi.inventory_item_id=l_item_id
and fsp.INVENTORY_ORGANIZATION_ID =
NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID)
and fsp.org_id=l_org_id;
|| ''
||'' || NL;
|| ''
||'' || NL;
|| ''
||'' || NL;
|| ''
||'' || NL;
|| ''
||'' || NL;
|| ''
||'' || NL;
|| ''
||'' || NL;
select msi.concatenated_segments
into l_item
from mtl_system_items_kfv msi,
financials_system_params_all fsp
where msi.inventory_item_id=l_item_id
and fsp.INVENTORY_ORGANIZATION_ID =
NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID)
and fsp.org_id=l_org_id;
select new_price
into l_new_price
from po_change_requests pcr
where pcr.change_request_group_id=p_group_id
and pcr.document_line_id= p_po_line_id
and pcr.request_level = 'LINE'
and new_price is not null;
select new_quantity
into l_new_quantity
from po_change_requests pcr
where pcr.change_request_group_id=p_group_id
and pcr.document_line_id= p_po_line_id
and pcr.document_line_location_id =p_po_shipment_id
and pcr.request_level = 'SHIPMENT'
and new_quantity is not null;
SELECT pcr.new_amount,(nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0)),pol.matching_basis
into l_tmp_new_amount, l_old_quantity,l_matching_basis
FROM po_change_requests pcr,
po_lines_all pol,
po_line_locations_all pll,
po_headers_all poh
WHERE pcr.change_request_group_id= p_group_id
AND pcr.request_status IN ('PENDING', 'BUYER_APP', 'ACCEPTED', 'REJECTED')
AND pol.po_line_id = p_line_id
AND pll.line_location_id = p_line_location_id
AND pcr.document_header_id=pol.po_header_id
AND pcr.document_line_id=pol.po_line_id
AND nvl(pcr.document_line_location_id,
-1)=pll.line_location_id(+)
AND pcr.request_level<>'DISTRIBUTION'
AND pol.from_header_id=poh.po_header_id(+);
select new_quantity
into l_new_quantity
from po_change_requests pcr
where pcr.change_request_group_id=p_group_id
and pcr.document_line_id= p_line_id
and pcr.document_line_location_id =p_line_location_id
and pcr.request_level = 'SHIPMENT'
and new_quantity is not null;
select distinct pcr.document_type
into l_document_type
from po_change_requests pcr
where pcr.change_request_group_id=p_group_id
and pcr.document_line_id= p_line_id
and pcr.document_line_location_id = p_line_location_id;
select
distinct prl.blanket_po_header_id
into
l_blanket_header_id
from
po_requisition_lines_all prl,
po_line_locations_all pll,
po_lines_all pol
where
pol.po_line_id = p_line_id and
pol.po_line_id = pll.po_line_id and
prl.line_location_id = pll.line_location_id;
select new_price
into l_new_price
from po_change_requests pcr
where pcr.change_request_group_id=p_group_id
and pcr.document_line_id= p_line_id
and pcr.request_level = 'LINE'
and new_price is not null;
select pol.po_header_id, pol.order_type_lookup_code
into l_po_header_id, l_po_order_type
from po_lines_all pol
where pol.po_line_id = p_line_id;
select pol.line_num,
pol.po_line_id,
pll.shipment_num,
pol.item_id,
pll.need_by_date old_need_by_date,
pcr.new_need_by_date,
nvl(pcr.old_price, nvl(pll.price_override, pol.unit_price)) old_price,
pcr.new_price new_price,
pol.quantity,
pll.quantity old_quantity,
pcr.old_quantity change_old_quantity,
pcr.new_quantity change_new_quantity,
pcr.action_type,
pol.item_description,
pol.unit_meas_lookup_code,
pll.unit_meas_lookup_code,
hla.location_code,
pcr.request_reason,
pol.org_id,
nvl(pcr.old_start_date, pol.start_date) old_start_date,
pcr.new_start_date,
nvl(pcr.old_expiration_date, pol.expiration_date) old_end_date,
pcr.new_expiration_date,
nvl(pcr.old_amount,
decode(pcr.document_line_location_id,
null, pol.amount,
nvl(pll.amount,
(nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))
*pll.price_override))) old_amount,
nvl(pcr.new_amount,
decode(pcr.document_line_location_id,
null, null, /* the calcuated amount will show at shipment level */
PO_ReqChangeRequestNotif_PVT.get_goods_shipment_new_amount(grp_id,
pol.po_line_id, pcr.document_line_location_id,
nvl(pcr.old_price, nvl(pll.price_override, pol.unit_price)),
(nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0)))))
from po_change_requests pcr,
po_lines_all pol,
po_line_locations_all pll,
hr_locations_all hla
where pcr.change_request_group_id=grp_id
and pcr.request_status = 'PENDING'
and pcr.document_header_id=pol.po_header_id
and pcr.document_line_id=pol.po_line_id
and nvl(pcr.document_line_location_id, -1)=pll.line_location_id(+)
and pll.ship_to_location_id=hla.location_id(+)
and pcr.request_level<>'DISTRIBUTION'
order by line_num, nvl(shipment_num, 0);
select document_header_id, po_release_id
from po_change_requests
where change_request_group_id=p_group_id;
select sum(decode(pcr.action_type, 'CANCELLATION', 0,
nvl(pcr.new_quantity, pll.quantity)))
from po_change_requests pcr,
po_line_locations_all pll
where pcr.change_request_group_id(+)=grp_id
and pcr.document_line_id(+)=line_id
and pcr.document_line_location_id(+)=pll.line_location_id
and pcr.request_level(+)='SHIPMENT'
and pll.po_line_id=line_id
and nvl(pll.cancel_flag,'N') <> 'Y'
and nvl(pll.closed_code,'OPEN') not in('FINALLY CLOSED');
select pll.ship_to_location_id,
nvl(pcr.new_need_by_date, pll.need_by_date),
poh.currency_code,
poh.rate_type,
nvl(pcr.action_type, 'A'),
poh.vendor_id,
poh.vendor_site_id,
poh.creation_date,
poh.po_header_id,
pol.po_line_id,
pol.line_type_id,
pol.item_revision,
pol.category_id,
pol.VENDOR_PRODUCT_NUM,
nvl(pol.base_unit_price, pol.unit_price),
nvl(pll.quantity_received,0),
nvl(pll.accrue_on_receipt_flag,'N'),
nvl(pll.quantity_billed,0)
from po_lines_all pol,
po_headers_all poh,
po_line_locations_all pll,
po_change_requests pcr
where pol.po_line_id=line_id
and pol.po_header_id=poh.po_header_id
and pll.po_line_id=line_id
and pll.line_location_id=pcr.document_line_location_id(+)
and pcr.request_level(+)='SHIPMENT'
and grp_id=pcr.change_request_group_id(+);
select count(1)
into l_num_of_changes
from (select distinct document_line_id, document_line_location_id
from po_change_requests
where change_request_group_id = l_grp_id
and action_type = 'MODIFICATION'
and request_status='PENDING');
select count(1) into l_num_of_cancels
from po_change_requests
where change_request_group_id = l_grp_id
and action_type = 'CANCELLATION'
and request_status='PENDING';
select segment1, revision_num, pos_totals_po_sv.get_po_total(po_header_id), currency_code,
vendor_id, vendor_site_id, creation_date, fob_lookup_code,
ship_via_lookup_code, ship_to_location_id, acceptance_required_flag,type_lookup_code
into
l_po_num, l_revision_num, l_po_total, l_po_currency,
l_vendor_id,l_vendor_site_id,l_order_date, l_fob,
l_carrier, l_ship_to_id, l_acceptance_required_flag,l_type_lookup_code
from po_headers_all
where po_header_id = l_header_id;
select ph.segment1, pr.release_num, pr.revision_num, pos_totals_po_sv.get_release_total(pr.po_release_id), ph.currency_code,
ph.vendor_id, ph.vendor_site_id, pr.creation_date, ph.fob_lookup_code,
ph.ship_via_lookup_code, ph.ship_to_location_id, pr.acceptance_required_flag,ph.type_lookup_code
into
l_blanket_num, l_release_num, l_revision_num, l_po_total, l_po_currency,
l_vendor_id,l_vendor_site_id,l_order_date, l_fob,
l_carrier, l_ship_to_id, l_acceptance_required_flag,l_type_lookup_code
from po_releases_all pr, po_headers_all ph
where pr.po_release_id = l_release_id
and pr.po_header_id = ph.po_header_id;
select vendor_name into l_supplier_name from po_vendors where vendor_id = l_vendor_id;
select address_line1, address_line2, address_line3, city,state,zip
into l_sup_address_line1, l_sup_address_line2, l_sup_address_line3, l_sup_city,
l_sup_state,l_sup_zip
from po_vendor_sites_all
where vendor_site_id = l_vendor_site_id;
select address_line_1, address_line_2, address_line_3, town_or_city, region_1, postal_code
into l_ship_addr_l1, l_ship_addr_l2, l_ship_addr_l3, l_ship_city, l_ship_state, l_ship_zip
from hr_locations_all
where location_id = l_ship_to_id;
select count(1)
into l_num_temp_labors
from po_change_requests pcr,
po_lines_all pol
where pcr.change_request_group_id=l_grp_id
and pcr.request_status = 'PENDING'
and pcr.document_header_id=pol.po_header_id
and pcr.document_line_id=pol.po_line_id
and pcr.request_level<>'DISTRIBUTION'
and pol.purchase_basis ='TEMP LABOR';
|| '/OA_MEDIA/newupdateditem_status.gif ALT="">'
|| fnd_message.get_string('PO', 'PO_WF_NOTIF_NEW_VALUE');
select
distinct prl.blanket_po_header_id,
prl.blanket_po_line_num
into
l_blanket_header_id, l_blanket_line_num
from
po_requisition_lines_all prl,
po_line_locations_all pll,
po_lines_all pol
where
pol.po_line_id = l_line_id and
pol.po_line_id = pll.po_line_id and
prl.line_location_id = pll.line_location_id;
select sum(decode(pcr.action_type, 'CANCELLATION', 0,
nvl(pcr.new_quantity, pll.quantity)))
from po_change_requests pcr,
po_line_locations_all pll
where pcr.change_request_group_id = grp_id
and pcr.document_line_id = line_id
and pcr.document_line_location_id = pll.line_location_id
and pcr.request_level = 'SHIPMENT'
and pll.po_line_id = line_id
and nvl(pll.cancel_flag,'N') <> 'Y'
and nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED');
select pll.ship_to_location_id,
nvl(pcr.new_need_by_date, pll.need_by_date),
poh.currency_code,
poh.rate_type,
nvl(pcr.action_type, 'A'),
poh.vendor_id,
poh.vendor_site_id,
poh.creation_date,
pol.po_header_id,
pol.po_line_id,
pol.line_type_id,
pol.item_revision,
pol.category_id,
pol.VENDOR_PRODUCT_NUM,
nvl(pol.base_unit_price, pol.unit_price),
nvl(pll.quantity_received,0),
nvl(pll.accrue_on_receipt_flag,'N'),
nvl(pll.quantity_billed,0),
pol.from_line_id,
pol.contract_id
from po_lines_all pol,
po_headers_all poh,
po_line_locations_all pll,
po_change_requests pcr
where pol.po_line_id=line_id
and pol.po_header_id=poh.po_header_id
and pll.po_line_id=line_id
and pll.line_location_id=pcr.document_line_location_id
and pcr.request_level = 'SHIPMENT'
and grp_id=pcr.change_request_group_id;
select distinct pcr.document_type
into l_document_type
from po_change_requests pcr
where pcr.change_request_group_id=p_group_id
and pcr.document_line_id= p_line_id
and pcr.document_line_location_id = p_line_location_id;
select
prl.blanket_po_header_id
into
l_blanket_header_id
from
po_requisition_lines_all prl
where
prl.line_location_id = p_line_location_id;
SELECT NVL(pcr.new_quantity, PLL.quantity),
PLL.ship_to_location_id,
nvl(pcr.new_need_by_date, pll.need_by_date),
PLL.po_line_id,
POL.price_break_lookup_code,
nvl(pll.quantity_received,0),
nvl(pll.accrue_on_receipt_flag,'N'),
nvl(pll.quantity_billed,0)
INTO l_release_shipment_quantity,
l_ship_to_loc_id,
l_ship_need_by,
l_from_line_id,
l_price_break_type,
l_quantity_received,
l_accrue_on_receipt_flag,
l_quantity_billed
FROM po_lines_all pol,
po_line_locations_all pll,
po_change_requests pcr
WHERE pcr.change_request_group_id = p_group_id
and pll.line_location_id = p_line_location_id
and pcr.document_line_location_id = pll.line_location_id
and pll.po_line_id = pol.po_line_id
and pcr.request_level(+)='SHIPMENT' ;
select distinct prl.blanket_po_header_id
into l_blanket_po_header_id
from po_requisition_lines_all prl,
po_line_locations_all pll,
po_lines_all pol
where pol.po_line_id = p_line_id
and pol.po_line_id = pll.po_line_id
and prl.line_location_id = pll.line_location_id
and pll.line_location_id = p_line_location_id;
select pcr.old_price, nvl(pcr.old_price,nvl(pll.price_override, pol.unit_price))
into l_pcr_old_price, l_price
FROM po_change_requests pcr,
po_lines_all pol,
po_line_locations_all pll,
po_headers_all poh
WHERE pcr.change_request_group_id= p_group_id
AND pcr.request_status IN ('PENDING', 'BUYER_APP', 'ACCEPTED', 'REJECTED')
AND pol.po_line_id = p_line_id
AND pll.line_location_id is null
AND pcr.document_header_id=pol.po_header_id
AND pcr.document_line_id=pol.po_line_id
AND nvl(pcr.document_line_location_id,-1)=pll.line_location_id(+)
AND pcr.request_level<>'DISTRIBUTION'
AND pol.from_header_id=poh.po_header_id(+);
select pcr.old_price, pol.unit_price,nvl(pcr.old_price,nvl(pll.price_override, pol.unit_price))
into l_pcr_old_price,l_pol_unit_price,l_price
FROM po_change_requests pcr,
po_lines_all pol,
po_line_locations_all pll,
po_headers_all poh
WHERE pcr.change_request_group_id= p_group_id
AND pcr.request_status IN ('PENDING', 'BUYER_APP', 'ACCEPTED', 'REJECTED')
AND pol.po_line_id = p_line_id
AND pll.line_location_id = p_line_location_id
AND pcr.document_header_id=pol.po_header_id
AND pcr.document_line_id=pol.po_line_id
AND nvl(pcr.document_line_location_id,-1)=pll.line_location_id(+)
AND pcr.request_level<>'DISTRIBUTION'
AND pol.from_header_id=poh.po_header_id(+);
select pol.po_header_id, pol.matching_basis,pol.order_type_lookup_code
into l_po_header_id, l_po_matching_basis,l_po_order_type
from po_lines_all pol
where pol.po_line_id = p_line_id;
SELECT sob.currency_code
INTO l_functional_currency_code
FROM gl_sets_of_books sob, financials_system_params_all fsp
WHERE fsp.org_id = p_org_id
AND fsp.set_of_books_id = sob.set_of_books_id;
select poh.currency_code,poh.rate
into l_po_currency_code,l_rate
from po_headers_all poh
where poh.po_header_id = p_po_header_id;