[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.notification_id,poh.wf_item_type,poh.wf_item_key, a.message_name
INTO v_nid,l_po_item_type,l_po_item_key, l_message_name
from wf_notifications a, po_headers_all poh,
wf_item_activity_statuses wa
where poh.po_header_id = p_po_header_id and
poh.wf_item_key = wa.item_key and
poh.wf_item_type = wa.item_type
and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
and a.status = 'OPEN'
and a.notification_id = wa.notification_id
and wa.activity_status = 'NOTIFIED';
select a.notification_id,por.wf_item_type,por.wf_item_key, a.message_name
INTO v_nid,l_po_item_type,l_po_item_key, l_message_name
from wf_notifications a, po_releases_all por,
wf_item_activity_statuses wa
where por.po_release_id = p_po_release_id and
por.wf_item_key = wa.item_key and
por.wf_item_type = wa.item_type
and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
and a.status = 'OPEN'
and a.notification_id = wa.notification_id
and wa.activity_status = 'NOTIFIED';
select a.notification_id
INTO v_nid
from wf_notifications a, wf_item_activity_statuses wa
where wa.item_key = p_item_key and
wa.item_type = p_item_type and
a.message_name = 'PO_SUPPLIER_SIGNATURE' and
a.status = 'OPEN' and
a.notification_id = wa.notification_id;
select change_request_group_id
from po_change_requests
where document_header_id = p_po_header_id and
request_status in ('NEW', 'PENDING') and
request_level in ('HEADER', 'LINE', 'SHIPMENT') and
document_type = p_document_type;
select change_request_group_id
from po_change_requests
where po_release_id = p_po_release_id and
request_status in ('NEW', 'PENDING') and
request_level in ('HEADER', 'LINE', 'SHIPMENT') and
document_type = p_document_type;
select po_chg_request_seq.nextval
into v_req_grp_id
from dual;
select wf_item_key,wf_item_type
into l_po_item_key,l_po_item_type
from po_headers_all
where po_header_id = p_po_header_id;
select wf_item_key,wf_item_type
into l_po_item_key,l_po_item_type
from po_releases_all
where po_release_id = p_po_release_id;
SELECT ACCEPTED_FLAG
INTO l_accepted_flag
FROM po_acceptances
WHERE po_header_id = p_po_header_id
AND REVISION_NUM = p_revision_num
AND PO_LINE_LOCATION_ID IS NULL
AND ACCEPTING_PARTY='S';
SELECT ACCEPTED_FLAG
INTO l_accepted_flag
FROM po_acceptances
WHERE po_release_id = p_po_release_id
AND REVISION_NUM = p_revision_num
AND PO_LINE_LOCATION_ID IS NULL
AND ACCEPTING_PARTY='S';
select po_chg_request_seq.nextval
into v_request_group_id
from dual;
insert into po_change_requests(
change_request_group_id, change_request_id,
initiator, action_type, request_reason,
request_level, request_status, document_type,
document_header_id, document_num,
document_revision_num, po_release_id,
created_by, creation_date,last_updated_by,last_update_date,
last_update_login,document_line_id, document_line_number,
document_line_location_id, document_shipment_number,
parent_line_location_id, document_distribution_id,
document_distribution_number,
old_quantity, new_quantity,
old_promised_date, new_promised_date,
old_supplier_part_number, new_supplier_part_number,
old_price, new_price, old_need_by_date, new_need_by_date,
old_supplier_reference_number, new_supplier_reference_number,
Approval_Required_Flag,Parent_Change_request_Id,
Requester_Id ,
OLD_SUPPLIER_ORDER_NUMBER , NEW_SUPPLIER_ORDER_NUMBER,
OLD_SUPPLIER_ORDER_LINE_NUMBER , NEW_SUPPLIER_ORDER_LINE_NUMBER,
change_active_flag, MSG_CONT_NUM, REQUEST_ORIGIN,ADDITIONAL_CHANGES,
OLD_START_DATE,NEW_START_DATE,OLD_EXPIRATION_DATE,NEW_EXPIRATION_DATE,
OLD_AMOUNT,NEW_AMOUNT,
SUPPLIER_DOC_REF, SUPPLIER_LINE_REF, SUPPLIER_SHIPMENT_REF, --added in FPJ for splits.
NEW_PROGRESS_TYPE,NEW_PAY_DESCRIPTION --<< Complex work changes for R12 >>
)
values (x_request_group_id,po_chg_request_seq.nextval,
p_po_change_requests(i).initiator,
p_po_change_requests(i).action_type,
p_po_change_requests(i).request_reason,
p_po_change_requests(i).request_level,
p_po_change_requests(i).request_status,
p_po_change_requests(i).document_type,
p_po_change_requests(i).document_header_id,
p_po_change_requests(i).document_num,
to_number(p_po_change_requests(i).document_revision_num),
p_po_change_requests(i).po_release_id,
l_user_id,sysdate,l_login_id,sysdate,l_login_id,
p_po_change_requests(i).document_line_id,
p_po_change_requests(i).document_line_number,
p_po_change_requests(i).document_line_location_id,
p_po_change_requests(i).document_shipment_number,
p_po_change_requests(i).parent_line_location_id,
p_po_change_requests(i).document_distribution_id,
p_po_change_requests(i).document_distribution_number,
p_po_change_requests(i).old_quantity,
p_po_change_requests(i).new_quantity,
p_po_change_requests(i).old_promised_date,
p_po_change_requests(i).new_promised_date,
p_po_change_requests(i).old_supplier_part_number,
p_po_change_requests(i).new_supplier_part_number,
p_po_change_requests(i).old_price,
p_po_change_requests(i).new_price,
p_po_change_requests(i).old_need_by_date,
p_po_change_requests(i).new_need_by_date,
p_po_change_requests(i).old_supplier_reference_number,
p_po_change_requests(i).new_supplier_reference_number,
p_po_change_requests(i).Approval_Required_Flag,
p_po_change_requests(i).Parent_Change_request_Id,
p_po_change_requests(i).Requester_id,
p_po_change_requests(i).Old_Supplier_Order_Number,
p_po_change_requests(i).New_Supplier_Order_Number,
p_po_change_requests(i).Old_Supplier_Order_Line_Number,
p_po_change_requests(i).New_Supplier_Order_Line_Number,
decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
p_chn_int_cont_num,
p_chn_source,
p_po_change_requests(i).Additional_changes,
p_po_change_requests(i).old_start_date,
p_po_change_requests(i).new_start_date,
p_po_change_requests(i).old_expiration_date,
p_po_change_requests(i).new_expiration_date,
p_po_change_requests(i).old_amount,
p_po_change_requests(i).new_amount,
p_po_change_requests(i).SUPPLIER_DOC_REF,
p_po_change_requests(i).SUPPLIER_LINE_REF,
p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
p_po_change_requests(i).NEW_PAY_DESCRIPTION
);
updatePoAttr boolean := false;
l_last_upd_date po_headers_all.last_update_date%type;
SELECT last_update_date
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = p_po_header_id
FOR UPDATE of last_update_date NOWAIT;
SELECT last_update_date
FROM PO_RELEASES_ALL
WHERE PO_RELEASE_ID = p_po_release_id
FOR UPDATE of last_update_date NOWAIT;
l_last_upd_date := relRec.last_update_date;
l_last_upd_date := poRec.last_update_date;
select agent_id,nvl(acceptance_required_flag,'N')
into v_buyer_id,x_accp_flag
from po_headers_all
where po_header_id = p_po_header_id;
select agent_id,nvl(acceptance_required_flag,'N')
into v_buyer_id,x_accp_flag
from po_releases_all
where po_release_id = p_po_release_id;
updatePoAttr := true;
updatePoAttr := true;
updatePoAttr := true;
update po_line_locations_all
set supplier_order_line_number = l_po_change_requests(i).New_Supplier_Order_Line_Number
where line_location_id = l_po_change_requests(i).document_line_location_id;
update po_headers_all
set vendor_order_num = l_po_change_requests(i).New_Supplier_Order_Number
where po_header_id = p_po_header_id;
update po_releases_all
set vendor_order_num = l_po_change_requests(i).New_Supplier_Order_Number
where po_release_id = p_po_release_id;
updatePoAttr := true;
if(updatePoAttr) then
IF g_fnd_debug = 'Y' THEN
IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
'.invoked', 'Update PO ' ||
', Header ID: ' || NVL(TO_CHAR(p_po_header_id),'null') ||
', Release ID: ' || NVL(TO_CHAR(p_po_release_id),'null'));
update_po_attributes(p_po_header_id,p_po_release_id,p_revision_num,
l_request_group_id, x_return_status, p_chn_requestor_username,
p_user_id,
p_login_id);
update po_releases_all set
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where po_release_id = p_po_release_id;
update po_headers_all set
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where po_header_id = p_po_header_id;
* Private Procedure: update_po_attributes
* Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM,REQUEST_GROUP_ID
* Modifies:
* Effects: Updates The PO_HEADERS_ALL, PO_RELEASES_ALL
* Returns:
* x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
* FND_API.G_RET_STS_ERROR if an error occurs
* FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
*/
procedure update_po_attributes
(p_po_header_id IN number,
p_po_release_id IN number,
p_revision_num IN number,
p_chg_request_grp_id IN number,
x_return_status OUT NOCOPY varchar2,
p_chn_requestor_username in varchar2 default null,
p_user_id IN number default null,
p_login_id IN number default null) is
l_api_name CONSTANT VARCHAR2(30) := 'update_po_attributes';
update po_headers_all set
authorization_status = 'IN PROCESS',
CHANGE_REQUESTED_BY = 'SUPPLIER',
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where po_header_id = p_po_header_id;
update po_line_locations_all
set approved_flag = 'R'
where line_location_id in (select document_line_location_id
from po_change_requests
where request_level = 'SHIPMENT' and
document_header_id = p_po_header_id and
action_type in ('MODIFICATION','CANCELLATION') and
initiator = 'SUPPLIER' and
request_status ='PENDING') and
approved_flag='Y';
update po_line_locations_all
set approved_flag = 'R'
where po_header_id in (select document_header_id
from po_change_requests
where request_level = 'HEADER' and
document_header_id = p_po_header_id and
action_type ='CANCELLATION' and
initiator = 'SUPPLIER' and
request_status ='PENDING') and
approved_flag='Y';
update po_line_locations_all
set approved_flag = 'R'
where po_line_id in (select document_line_id
from po_change_requests
where request_level = 'LINE' and
document_header_id = p_po_header_id and
request_status = 'PENDING' and
initiator = 'SUPPLIER' and
action_type = 'MODIFICATION' and
new_price is not null) and
approved_flag='Y'
and po_release_id is null; --This condition added for bug 8768745
line_location_id not in (select document_line_location_id
from po_change_requests
where request_level = 'SHIPMENT' and
document_header_id = p_po_header_id and
action_type = 'CANCELLATION' and
initiator = 'SUPPLIER' and
request_status ='PENDING') ;
update po_releases_all set
authorization_status = 'IN PROCESS',
CHANGE_REQUESTED_BY = 'SUPPLIER',
revised_date = sysdate,
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where po_release_id = p_po_release_id;
update po_line_locations_all
set approved_flag = 'R'
where line_location_id in (select document_line_location_id
from po_change_requests
where request_level = 'SHIPMENT' and
po_release_id = p_po_release_id and
action_type in ('MODIFICATION','CANCELLATION') and
initiator = 'SUPPLIER' and
request_status = 'PENDING') and
approved_flag='Y';
update po_line_locations_all
set approved_flag = 'R'
where po_release_id in (select po_release_id
from po_change_requests
where request_level = 'HEADER' and
po_release_id = p_po_release_id and
action_type ='CANCELLATION' and
initiator = 'SUPPLIER' and
request_status ='PENDING') and
approved_flag='Y';
END update_po_attributes;
select po_line_id
from po_lines_archive_all pol
where pol.po_header_id = p_po_header_id and
pol.latest_external_flag='Y' and
nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED') and
nvl(pol.cancel_flag,'N') <> 'Y' and
nvl(pol.MANUAL_PRICE_CHANGE_FLAG,'N') <> 'Y' AND /* 9867085 */
pol.from_header_id in (
select po_header_id
from po_headers_all poh
where poh.global_agreement_flag='Y'
and poh.po_header_id=pol.from_header_id) and
exists(select poll.line_location_id
from po_line_locations_archive_all poll
where poll.po_line_id = pol.po_line_id and
nvl(poll.closed_code,'OPEN') not in('FINALLY CLOSED') and
nvl(poll.cancel_flag,'N') <> 'Y' and
poll.latest_external_flag='Y');
select line_location_id,quantity
from po_line_locations_archive_all
where po_line_id = p_line_id and
nvl(closed_code,'OPEN') not in('FINALLY CLOSED') and
nvl(cancel_flag,'N') <> 'Y' 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 , org_id
into v_type_code , x_org_id
from po_headers_all
where po_header_id= p_po_header_id;
SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
INTO l_price_break_type
FROM po_lines_all
WHERE po_line_id = p_po_change_requests(i).document_line_id;
select ship_to_location_id, ship_to_organization_id,need_by_date
into l_shipToLoc,l_shipToOrg,l_needBydate
from po_line_locations_archive_all
where shipment_num = (select min(shipment_num)
from po_line_locations_archive_all
where po_line_id = l_ga_lineId and
nvl(closed_code,'OPEN') not in('FINALLY CLOSED') and
nvl(cancel_flag,'N') <> 'Y' and
latest_external_flag='Y' ) and
latest_external_flag='Y' and
po_line_id = l_ga_lineId ;
select nvl(allow_price_override_flag,'N')
into x_price_change
from po_lines_all
where po_line_id =
(select from_line_id
from po_lines_all
where po_line_id=p_po_line_id);
select poh.type_lookup_code
into l_doc_type
from po_headers_all poh, po_lines_all pol
where poh.po_header_id = pol.po_header_id
and po_line_id = p_po_line_id;
select polt.ORDER_TYPE_LOOKUP_CODE,PO_HEADER_ID
into x_order_type,x_po_header_id
from po_lines_all pol,po_line_types polt
where pol.po_line_id = p_po_line_id and
pol.line_type_id = polt.line_type_id;
PO_DOCUMENT_CHECKS_GRP.check_std_po_price_updateable (
p_api_version => 1.0,
x_return_status =>l_return_status,
p_po_line_id => p_po_line_id,
p_from_price_break => 'N',
p_add_reasons_to_msg_list => 'N',
x_price_updateable => x_price_change,
x_retroactive_price_change => l_retro_active_price_change);
select poh.type_lookup_code,por.org_id
into v_type_code,x_org_id
from po_headers_all poh,po_releases_all por
where por.po_header_id = poh.po_header_id and
por.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 count(*)
INTO x_ship_count
FROM PO_LINE_LOCATIONS_ALL POLL,PO_LINES_ALL POL
WHERE POLL.po_release_id = p_po_release_id
AND POLL.po_line_id = POL.po_line_id
AND nvl(POLL.cancel_flag, 'N') = 'N'
AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND POLL.shipment_type in ('SCHEDULED', 'BLANKET')
AND (nvl(POLL.quantity_received,0) >= nvl(POLL.quantity,0)
OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity,0)
OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity_received,0))
AND POL.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE');
SELECT count(*)
INTO x_ship_count
FROM PO_LINE_LOCATIONS_ALL POLL,PO_LINES_ALL POL,PO_HEADERS_ALL POH
WHERE
POH.PO_HEADER_ID = p_po_header_id
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.TYPE_LOOKUP_CODE ='STANDARD'
AND POLL.po_line_id = POL.po_line_id
AND nvl(POLL.cancel_flag, 'N') = 'N'
AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND (nvl(POLL.quantity_received,0) >= nvl(POLL.quantity,0)
OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity,0)
OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity_received,0))
AND POL.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')
AND NVL(POLL.payment_type,' ') <> 'ADVANCE'; --
select DECODE( nvl(pll.cancel_flag,'N'),
'Y',fnd_message.get_string('POS','POS_PO_CANCELLED'),
'N',DECODE(NVL(pll.CONSIGNED_FLAG,'N'),
'Y',DECODE(NVL(pll.CLOSED_CODE,'OPEN'),'CLOSED FOR INVOICE',
DECODE(
PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
(1.0,FND_API.G_FALSE,pll.line_location_id,
pll.po_header_id, p_po_release_id,p_revision_num),
'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
'',polc.displayed_field),
polc.displayed_field
),
'N',
CASE
when NVL(pll.CLOSED_CODE,'OPEN')='OPEN' OR NVL(pll.CLOSED_CODE,'OPEN')='CLOSED FOR RECEIVING'
OR NVL(pll.CLOSED_CODE,'OPEN') = 'CLOSED FOR INVOICE'
THEN
DECODE(
PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
(1.0,FND_API.G_FALSE,pll.line_location_id,
pll.po_header_id, p_po_release_id,p_revision_num),
'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
'',polc.displayed_field)
ELSE polc.displayed_field
END
)
) ,
nvl(pll.closed_code,'OPEN'),
PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
(1.0,FND_API.G_FALSE,pll.line_location_id,
pll.po_header_id, p_po_release_id,p_revision_num),nvl(poh.acceptance_required_flag,'N'),
poh.revision_num,nvl(pll.consigned_flag,'N')
into x_msg_display,x_msg_code,x_ack_stat,x_accp_flag,x_revision,x_cons_flag
from po_line_locations_all pll,
po_headers_all poh,
po_lookup_codes polc
where
polc.lookup_code = NVL(pll.closed_code, 'OPEN') and
polc.lookup_type = 'DOCUMENT STATE' and
poh.po_header_id = pll.po_header_id and
pll.line_location_id = p_line_location_id ;
select note into x_note
from po_acceptances
where po_line_location_id=p_line_location_id and
revision_num = x_revision;
select DECODE( nvl(pll.cancel_flag,'N'),
'Y',fnd_message.get_string('POS','POS_PO_CANCELLED'),
'N',DECODE(NVL(pll.CONSIGNED_FLAG,'N'),
'Y',DECODE(NVL(pll.CLOSED_CODE,'OPEN'),'CLOSED FOR INVOICE',
DECODE(
PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
(1.0,FND_API.G_FALSE,pll.line_location_id,
pll.po_header_id, p_po_release_id,p_revision_num),
'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
'',polc.displayed_field),
polc.displayed_field
),
'N',
CASE
when NVL(pll.CLOSED_CODE,'OPEN')='OPEN' OR NVL(pll.CLOSED_CODE,'OPEN')='CLOSED FOR RECEIVING'
OR NVL(pll.CLOSED_CODE,'OPEN') = 'CLOSED FOR INVOICE'
THEN
DECODE(
PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
(1.0,FND_API.G_FALSE,pll.line_location_id,
pll.po_header_id, p_po_release_id,p_revision_num),
'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
'',polc.displayed_field)
ELSE polc.displayed_field
END
)
) ,
nvl(pll.closed_code,'OPEN'),
PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
(1.0,FND_API.G_FALSE,pll.line_location_id,
pll.po_header_id, p_po_release_id,p_revision_num),nvl(por.acceptance_required_flag,'N'),
por.revision_num,nvl(pll.consigned_flag,'N')
into x_msg_display,x_msg_code,x_ack_stat,x_accp_flag,x_revision,x_cons_flag
from po_line_locations_all pll,
po_releases_all por,
po_lookup_codes polc
where
polc.lookup_code = NVL(pll.closed_code, 'OPEN') and
polc.lookup_type = 'DOCUMENT STATE' and
por.po_header_id = pll.po_header_id and
por.po_release_id = p_po_release_id and
pll.line_location_id = p_line_location_id ;
select note into x_note
from po_acceptances
where po_line_location_id=p_line_location_id and
revision_num = x_revision;
select FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE')
into x_msg_display
from dual;
update_po_attributes(p_po_header_id,
p_po_release_id,
p_revision_num,
l_request_group_id,
x_return_status);
SELECT segment1
INTO x_ga_number
FROM po_headers_all
WHERE po_header_id = p_from_header_id
AND global_agreement_flag='Y';
SELECT UN_NUMBER
INTO x_un_number
FROM PO_UN_NUMBERS_TL
WHERE UN_NUMBER_ID = p_un_number_id
AND LANGUAGE = USERENV('LANG');
SELECT DESCRIPTION
INTO x_haz_class_desc
FROM PO_HAZARD_CLASSES_TL
WHERE HAZARD_CLASS_ID = p_haz_class_id
AND LANGUAGE = USERENV('LANG');
select change_request_group_id,DOCUMENT_REVISION_NUM
from po_change_requests
where document_header_id = p_po_header_id and
document_type = 'PO' and
change_active_flag= 'Y' and
initiator = 'SUPPLIER' and
request_status not in ('ACCEPTED', 'REJECTED');
select change_request_group_id,DOCUMENT_REVISION_NUM
from po_change_requests
where po_release_id = p_po_release_id and
document_type = 'RELEASE' and
change_active_flag= 'Y' and
initiator = 'SUPPLIER' and
request_status not in ('ACCEPTED', 'REJECTED');
update po_change_requests
set request_status = 'REJECTED',change_active_flag = 'N',
request_reason=l_cancel_msg
where po_release_id = p_po_release_id and
initiator = 'SUPPLIER' and
request_status not in ('ACCEPTED','REJECTED') and
action_type='MODIFICATION';
update po_change_requests
set request_status = 'ACCEPTED',
change_active_flag = 'N'
where initiator = 'SUPPLIER' and
request_status not in ('ACCEPTED','REJECTED') and
action_type='CANCELLATION' and
po_release_id = p_po_release_id;
update po_change_requests
set request_status = 'REJECTED',
change_active_flag = 'N',
request_reason=l_cancel_msg
where document_header_id = p_po_header_id and
request_status not in ('ACCEPTED','REJECTED') and
initiator = 'SUPPLIER' and
action_type='MODIFICATION';
update po_change_requests
set request_status = 'ACCEPTED',
change_active_flag = 'N'
where request_status not in ('ACCEPTED','REJECTED') and
initiator = 'SUPPLIER' and
action_type='CANCELLATION' and
document_header_id = p_po_header_id ;
update po_change_requests
set request_status = 'REJECTED',change_active_flag = 'N',request_reason=l_cancel_msg
where document_line_location_id = p_po_line_location_id and
request_level = 'SHIPMENT' and
request_status not in ('ACCEPTED','REJECTED') and
initiator = 'SUPPLIER' and
action_type='MODIFICATION';
update po_change_requests
set request_status = 'ACCEPTED',change_active_flag = 'N'
where document_line_location_id = p_po_line_location_id and
request_level = 'SHIPMENT' and
request_status not in ('ACCEPTED','REJECTED') and
initiator = 'SUPPLIER' and
action_type='CANCELLATION';
update po_change_requests
set request_status='REJECTED',change_active_flag='N',request_reason=l_cancel_msg
where document_line_id = p_po_line_id and
request_status not in ('ACCEPTED','REJECTED') and
initiator = 'SUPPLIER' and
action_type='MODIFICATION';
update po_change_requests
set request_status='ACCEPTED',change_active_flag='N'
where document_line_id = p_po_line_id and
request_status not in ('ACCEPTED','REJECTED') and
initiator = 'SUPPLIER' and
action_type='CANCELLATION';
update po_releases_all set
authorization_status = 'APPROVED',
CHANGE_REQUESTED_BY = null,
revised_date = sysdate,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where po_release_id = p_po_release_id;
select revision_num
into l_revision_num
from po_releases_all
where po_release_id = p_po_release_id;
update po_change_requests
set document_revision_num = l_revision_num
where po_release_id = p_po_release_id and
request_status not in ('ACCEPTED','REJECTED') and
document_type = 'RELEASE' and
change_active_flag= 'Y' and
initiator = 'SUPPLIER' ;
update po_headers_all set
authorization_status = 'APPROVED',
CHANGE_REQUESTED_BY = null,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where po_header_id = p_po_header_id;
select revision_num
into l_revision_num
from po_headers_all
where po_header_id = p_po_header_id;
update po_change_requests
set document_revision_num=l_revision_num
where document_header_id = p_po_header_id and
request_status not in ('ACCEPTED','REJECTED') and
document_type = 'PO' and
change_active_flag= 'Y' and
initiator = 'SUPPLIER' ;
select wf_item_type
into l_item_type
from po_headers_all
WHERE po_header_id = p_po_header_id;
select wf_item_type
into l_item_type
from po_headers_merge_v
WHERE po_header_id = p_po_header_id AND
draft_id = p_draft_id;
select max(shipment_num)
into v_ship_num
from po_line_locations_All
where po_line_id = p_po_line_id
group by po_line_id;
function getLastUpdateDate (
p_header_id IN NUMBER,
p_release_id in NUMBER)
return DATE IS
p_last_update_date DATE;
select last_update_Date
into p_last_update_date
from po_releases_All where
po_release_id = p_release_id
and rownum=1;
select last_update_Date
into p_last_update_date
from po_headers_All
where po_header_id = p_header_id
and rownum=1;
return p_last_update_Date;
'PO_CHG_REQUEST_PVT.getLastUpdateDate',
v_progress,
sqlcode );
select count(*)
into l_count_asn
from RCV_TRANSACTIONS_INTERFACE rti
where rti.TRANSACTION_TYPE = 'SHIP' and
rti.PROCESSING_STATUS_CODE = 'PENDING' and
rti.quantity > 0 and
rti.PO_HEADER_ID = p_po_header_id and
rti.po_release_id (+) = p_po_release_id and
(rti.po_line_location_id = l_po_change_requests(j).document_line_location_id OR l_po_change_requests(j).document_line_location_id is null);
select count(*)
into l_count_asn
from RCV_TRANSACTIONS_INTERFACE rti
where rti.TRANSACTION_TYPE = 'SHIP' and
rti.PROCESSING_STATUS_CODE = 'PENDING' and
rti.quantity > 0 and
rti.PO_HEADER_ID = p_po_header_id AND
(rti.po_line_location_id = l_po_change_requests(j).document_line_location_id OR l_po_change_requests(j).document_line_location_id is null);
SELECT count(*)
into l_count_asn
FROM RCV_SHIPMENT_LINES RSL
WHERE RSL.po_header_id = p_po_header_id
AND RSL.po_release_id (+) = p_po_release_id
AND (RSL.po_line_location_id = l_po_change_requests(j).document_line_location_id OR l_po_change_requests(j).document_line_location_id is null)
AND NVL(RSL.quantity_shipped,0) > NVL(RSL.quantity_received,0)
AND NVL(RSL.ASN_LINE_FLAG,'N') = 'Y'
AND NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED') <> 'CANCELLED';
SELECT count(*)
into l_count_asn
FROM RCV_SHIPMENT_LINES RSL
WHERE RSL.po_header_id = p_po_header_id
AND (RSL.po_line_location_id = l_po_change_requests(j).document_line_location_id OR l_po_change_requests(j).document_line_location_id is null)
AND NVL(RSL.quantity_shipped,0) > NVL(RSL.quantity_received,0)
AND NVL(RSL.ASN_LINE_FLAG,'N') = 'Y'
AND NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED') <> 'CANCELLED';
SELECT nvl(POLL.quantity, 0), nvl(POLL.quantity_billed, 0), nvl(POLL.quantity_received, 0)
into l_quan_ordered, l_quan_billed, l_quan_recd
FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL
WHERE POLL.line_location_id = l_po_change_requests(j).document_line_location_id
AND POLL.po_line_id = POL.po_line_id
AND nvl(POLL.cancel_flag, 'N') = 'N'
AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND nvl(POLL.receipt_required_flag, 'Y') <> 'N'
AND nvl(POLL.quantity_billed, 0) > nvl(POLL.quantity_received,0);
SELECT nvl(POLL.quantity, 0), nvl(POLL.quantity_billed, 0), nvl(POLL.quantity_received, 0)
into l_quan_ordered, l_quan_billed, l_quan_recd
FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL
WHERE POLL.line_location_id = l_po_change_requests(j).document_line_location_id
AND POLL.po_line_id = POL.po_line_id
AND nvl(POLL.cancel_flag, 'N') = 'N'
AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND nvl(POLL.receipt_required_flag, 'Y') <> 'N'
AND nvl(POLL.quantity_billed, 0) > nvl(POLL.quantity,0);
SELECT line_location_id,shipment_num
FROM po_line_locations_all
WHERE po_line_id = p_po_line_id;
SELECT Count(*)
INTO l_count_asn
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.quantity > 0
AND rti.po_header_id = p_po_header_id
AND rti.po_release_id (+) = p_po_release_id
AND rti.po_line_location_id =l_po_change_requests(j).document_line_location_id;
SELECT Count(*)
INTO l_count_asn
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.quantity > 0
AND rti.po_header_id = p_po_header_id
AND rti.po_line_location_id =l_po_change_requests(j).document_line_location_id;
SELECT Count(*)
INTO l_count_asn
FROM rcv_shipment_lines RSL
WHERE RSL.po_header_id = p_po_header_id
AND RSL.po_release_id (+) = p_po_release_id
AND RSL.po_line_location_id =l_po_change_requests(j).document_line_location_id
AND Nvl(RSL.quantity_shipped, 0) > Nvl(RSL.quantity_received, 0)
AND Nvl(RSL.asn_line_flag, 'N') = 'Y'
AND Nvl(RSL.shipment_line_status_code, 'EXPECTED') <>'CANCELLED';
SELECT Count(*)
INTO l_count_asn
FROM rcv_shipment_lines RSL
WHERE RSL.po_header_id = p_po_header_id
AND RSL.po_line_location_id = l_po_change_requests(j).document_line_location_id
AND Nvl(RSL.quantity_shipped, 0) > Nvl(RSL.quantity_received, 0)
AND Nvl(RSL.asn_line_flag, 'N') = 'Y'
AND Nvl(RSL.shipment_line_status_code, 'EXPECTED') <>'CANCELLED';
SELECT Count(*)
INTO l_count_asn
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.quantity > 0
AND rti.po_header_id = p_po_header_id
AND rti.po_release_id (+) = p_po_release_id
AND rti.po_line_location_id = ship_rec.line_location_id;
SELECT Count(*)
INTO l_count_asn
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.quantity > 0
AND rti.po_header_id = p_po_header_id
AND rti.po_line_location_id = ship_rec.line_location_id;
SELECT Count(*)
INTO l_count_asn
FROM rcv_shipment_lines RSL
WHERE RSL.po_header_id = p_po_header_id
AND RSL.po_release_id (+) = p_po_release_id
AND RSL.po_line_location_id = ship_rec.line_location_id
AND Nvl(RSL.quantity_shipped, 0) > Nvl(RSL.quantity_received, 0)
AND Nvl(RSL.asn_line_flag, 'N') = 'Y'
AND Nvl(RSL.shipment_line_status_code, 'EXPECTED') <>'CANCELLED';
SELECT Count(*)
INTO l_count_asn
FROM rcv_shipment_lines RSL
WHERE RSL.po_header_id = p_po_header_id
AND RSL.po_line_location_id = ship_rec.line_location_id
AND Nvl(RSL.quantity_shipped, 0) > Nvl(RSL.quantity_received, 0)
AND Nvl(RSL.asn_line_flag, 'N') = 'Y'
AND Nvl(RSL.shipment_line_status_code, 'EXPECTED') <>'CANCELLED' ;