[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_PriceBreakRows(p_chn_grp_id IN NUMBER);
PROCEDURE Insert_LineQuantityOrAmount(p_chn_grp_id IN NUMBER);
PROCEDURE insert_reqchange(p_change_table change_tbl_type,
p_chn_req_grp_id NUMBER);
PROCEDURE update_recordswithtax(p_chn_req_grp_id NUMBER);
PROCEDURE update_internalrecordswithtax(p_chn_req_grp_id NUMBER);
PROCEDURE insert_pricebreakrows(p_chn_grp_id IN NUMBER)
IS
l_api_name VARCHAR2(50) := 'Insert_PriceBreakRows';
SELECT
DISTINCT
pcr.document_header_id,
pcr.document_num,
pcr.document_revision_num,
pcr.document_line_id,
pcr.document_line_number,
pcr.requester_id
FROM po_change_requests pcr,
po_requisition_lines_all prla
WHERE pcr.change_request_group_id = grp_id
AND pcr.action_type = 'MODIFICATION'
AND prla.requisition_line_id = pcr.document_line_id
AND prla.blanket_po_header_id IS NOT NULL;
SELECT
prla.need_by_date,
prla.unit_meas_lookup_code,
prla.unit_price,
prla.currency_unit_price,
prla.blanket_po_header_id,
prla.blanket_po_line_num,
prla.deliver_to_location_id,
prla.destination_organization_id,
prla.currency_code,
prla.rate_type,
prla.org_id,
prla.vendor_id,
prla.vendor_site_id,
prla.creation_date,
plla.po_header_id,
plla.po_line_id,
prla.line_type_id,
prla.item_revision,
prla.item_id,
prla.category_id,
prla.supplier_ref_number,
prla.unit_price,
nvl(plla.quantity_received, 0),
nvl(plla.accrue_on_receipt_flag, 'N'),
nvl(plla.quantity_billed, 0)
INTO
l_old_date,
l_req_uom,
l_old_price,
l_old_curr_price,
l_source_doc_header_id,
l_source_doc_line_num,
l_deliver_to_loc_id,
l_destination_org_id,
l_req_currency_code,
l_req_rate_type,
l_org_id,
l_supplier_id,
l_supplier_site_id,
l_creation_date,
l_order_header_id,
l_order_line_id,
l_line_type_id,
l_item_revision,
l_item_id,
l_category_id,
l_supplier_item_num,
l_in_price,
l_quantity_received,
l_accrue_on_receipt_flag,
l_quantity_billed
FROM
po_requisition_lines_all prla,
po_line_locations_all plla
WHERE prla.requisition_line_id = l_req_line_id
AND prla.line_location_id = plla.line_location_id;
SELECT new_need_by_date
INTO l_new_date
FROM po_change_requests
WHERE new_need_by_date IS NOT NULL
AND change_request_group_id = p_chn_grp_id
AND document_line_id = l_req_line_id;
SELECT nvl(SUM(new_quantity), 0)
INTO l_new_qty
FROM po_change_requests
WHERE new_quantity IS NOT NULL
AND change_request_group_id = p_chn_grp_id
AND document_line_id = l_req_line_id
AND action_type = 'MODIFICATION'
AND request_level = 'DISTRIBUTION';
SELECT nvl(SUM(req_line_quantity), 0)
INTO l_old_qty
FROM po_req_distributions_all
WHERE requisition_line_id = l_req_line_id
AND distribution_id NOT IN(SELECT document_distribution_id
FROM po_change_requests
WHERE new_quantity IS NOT NULL
AND change_request_group_id = p_chn_grp_id
AND document_line_id = l_req_line_id
AND action_type = 'MODIFICATION'
AND request_level = 'DISTRIBUTION');
INSERT INTO po_change_requests
(
change_request_group_id,
change_request_id,
initiator,
action_type,
request_level,
request_status,
document_type,
document_header_id,
document_num,
document_revision_num,
created_by,
creation_date,
document_line_id,
document_line_number,
old_price,
new_price,
old_currency_unit_price,
new_currency_unit_price,
last_updated_by,
last_update_date,
last_update_login,
requester_id,
change_active_flag)
VALUES
(
p_chn_grp_id,
po_chg_request_seq.nextval,
'REQUESTER',
'DERIVED',
'LINE',
'SYSTEMSAVE',
'REQ',
l_document_header_id,
l_document_num,
l_document_revision_num,
l_req_user_id,
SYSDATE,
l_req_line_id,
l_document_line_number,
l_old_price,
l_new_price,
l_old_curr_price,
l_new_curr_price,
l_req_user_id,
SYSDATE,
l_req_user_id,
l_requester_id,
'Y'
);
END insert_pricebreakrows;
PROCEDURE insert_linequantityoramount(p_chn_grp_id IN NUMBER)
IS
l_api_name VARCHAR2(50) := 'Insert_LineQuantityOrAmount';
SELECT DISTINCT
document_header_id,
document_num,
document_line_id,
document_line_number,
requester_id
FROM po_change_requests
WHERE action_type = 'MODIFICATION'
AND change_request_group_id = grp_id;
SELECT change_request_id
FROM po_change_requests
WHERE document_line_id = line_id
AND change_request_group_id = grp_id
AND new_quantity IS NOT NULL
AND request_level = 'DISTRIBUTION';
SELECT change_request_id
FROM po_change_requests
WHERE document_line_id = line_id
AND change_request_group_id = grp_id
AND new_amount IS NOT NULL
AND request_level = 'DISTRIBUTION';
SELECT matching_basis, quantity, amount, currency_amount
INTO l_matching_basis, l_old_quantity, l_old_amount, l_old_cur_amount
FROM po_requisition_lines_all
WHERE requisition_line_id = l_line_id;
SELECT SUM(amount)
INTO l_new_amount
FROM (
SELECT new_amount amount
FROM po_change_requests
WHERE change_request_group_id = p_chn_grp_id
AND document_line_id = l_line_id
AND new_amount IS NOT NULL
AND request_level = 'DISTRIBUTION'
UNION ALL
SELECT req_line_amount amount
FROM po_req_distributions_all
WHERE requisition_line_id = l_line_id
AND distribution_id NOT IN
(SELECT document_distribution_id
FROM po_change_requests
WHERE change_request_group_id = p_chn_grp_id
AND document_line_id = l_line_id
AND new_amount IS NOT NULL
AND request_level = 'DISTRIBUTION')
);
SELECT SUM(amount)
INTO l_new_cur_amount
FROM (
SELECT new_currency_amount amount
FROM po_change_requests
WHERE change_request_group_id = p_chn_grp_id
AND document_line_id = l_line_id
AND new_currency_amount IS NOT NULL
AND request_level = 'DISTRIBUTION'
UNION ALL
SELECT req_line_currency_amount amount
FROM po_req_distributions_all
WHERE requisition_line_id = l_line_id
AND distribution_id NOT IN
(SELECT document_distribution_id
FROM po_change_requests
WHERE change_request_group_id = p_chn_grp_id
AND document_line_id = l_line_id
AND new_currency_amount IS NOT NULL
AND request_level = 'DISTRIBUTION')
);
SELECT SUM(quantity)
INTO l_new_quantity
FROM (
SELECT new_quantity quantity
FROM po_change_requests
WHERE change_request_group_id = p_chn_grp_id
AND document_line_id = l_line_id
AND new_quantity IS NOT NULL
AND request_level = 'DISTRIBUTION'
UNION ALL
SELECT req_line_quantity quantity
FROM po_req_distributions_all
WHERE requisition_line_id = l_line_id
AND distribution_id NOT IN
(SELECT document_distribution_id
FROM po_change_requests
WHERE change_request_group_id = p_chn_grp_id
AND document_line_id = l_line_id
AND new_quantity IS NOT NULL
AND request_level = 'DISTRIBUTION')
);
INSERT INTO po_change_requests
(
change_request_group_id,
change_request_id,
initiator,
action_type,
request_level,
request_status,
document_type,
document_header_id,
document_num,
created_by,
creation_date,
document_line_id,
document_line_number,
old_quantity,
new_quantity,
old_amount,
new_amount,
old_currency_amount,
new_currency_amount,
last_updated_by,
last_update_date,
last_update_login,
requester_id,
change_active_flag)
VALUES
(
p_chn_grp_id,
po_chg_request_seq.nextval,
'REQUESTER',
'DERIVED',
'LINE',
'SYSTEMSAVE',
'REQ',
l_req_header_id,
l_req_num,
l_req_user_id,
SYSDATE,
l_line_id,
l_line_num,
l_old_quantity,
l_new_quantity,
l_old_amount,
l_new_amount,
l_old_cur_amount,
l_new_cur_amount,
l_req_user_id,
SYSDATE,
l_req_user_id,
l_requester_id,
'Y'
);
END insert_linequantityoramount;
/* SELECT
quantity_delivered,
quantity_billed
INTO
l_qty_old_del,
l_qty_old_bill
FROM po_distributions_all
WHERE po_distribution_id = p_po_change_table(s).document_distribution_id;
select
dist.quantity_delivered * Decode(line.ORDER_TYPE_LOOKUP_CODE,'AMOUNT', Nvl(dist.rate,1),1), dist.quantity_billed * Decode(line.ORDER_TYPE_LOOKUP_CODE,'AMOUNT', Nvl(dist.rate,1),1)
into
l_qty_old_del,
l_qty_old_bill
from po_distributions_all dist, po_lines_all line
where dist.po_line_id = line.po_line_id AND
dist.po_distribution_id = p_po_change_table(s).document_distribution_id;
SELECT
prla.line_num,
prda.distribution_num,
prla.requisition_line_id,
prda.distribution_id
INTO
l_err_req_line_num,
l_err_req_dist_num,
l_err_req_line_id,
l_err_req_dist_id
FROM
po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda,
po_requisition_lines_all prla,
po_req_distributions_all prda
WHERE
pla.po_header_id = p_header_id
AND pla.po_line_id = p_po_change_table(s).document_line_id
AND plla.po_line_id = pla.po_line_id
AND plla.line_location_id = p_po_change_table(s).document_line_location_id
AND pda.line_location_id = plla.line_location_id
AND pda.po_distribution_id = p_po_change_table(s).document_distribution_id
AND pda.req_distribution_id = prda.distribution_id
AND prda.requisition_line_id = prla.requisition_line_id;
SELECT
prla.line_num,
prda.distribution_num,
prla.requisition_line_id,
prda.distribution_id
INTO
l_err_req_line_num,
l_err_req_dist_num,
l_err_req_line_id,
l_err_req_dist_id
FROM
po_line_locations_all plla,
po_distributions_all pda,
po_requisition_lines_all prla,
po_req_distributions_all prda
WHERE
plla.po_release_id = p_release_id
AND plla.line_location_id = p_po_change_table(s).document_line_location_id
AND pda.line_location_id = plla.line_location_id
AND pda.po_distribution_id = p_po_change_table(s).document_distribution_id
AND pda.req_distribution_id = prda.distribution_id
AND prda.requisition_line_id = prla.requisition_line_id;
SELECT plla.quantity_received
INTO l_qty_old_rec
FROM
po_line_locations_all plla,
po_distributions_all pda
WHERE plla.line_location_id = pda.line_location_id
AND pda.po_distribution_id = p_po_change_table(s).document_distribution_id;
SELECT SUM(plla.quantity_received) + p_po_change_table(s).new_quantity
INTO l_qty_new_rec
FROM
po_line_locations_all plla,
po_distributions_all pda1,
po_distributions_all pda2
WHERE plla.line_location_id = pda1.line_location_id
AND pda1.po_distribution_id <> p_po_change_table(s).document_distribution_id
AND pda1.line_location_id = pda2.line_location_id
AND pda2.po_distribution_id = p_po_change_table(s).document_distribution_id;
SELECT
prla.line_num,
prda.distribution_num,
prla.requisition_line_id,
prda.distribution_id
INTO
l_err_req_line_num,
l_err_req_dist_num,
l_err_req_line_id,
l_err_req_dist_id
FROM
po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda,
po_requisition_lines_all prla,
po_req_distributions_all prda
WHERE
pla.po_header_id = p_header_id
AND pla.po_line_id = p_po_change_table(s).document_line_id
AND plla.po_line_id = pla.po_line_id
AND plla.line_location_id = p_po_change_table(s).document_line_location_id
AND pda.line_location_id = plla.line_location_id
AND pda.po_distribution_id = p_po_change_table(s).document_distribution_id
AND pda.req_distribution_id = prda.distribution_id
AND prda.requisition_line_id = prla.requisition_line_id;
SELECT
prla.line_num,
prda.distribution_num,
prla.requisition_line_id,
prda.distribution_id
INTO
l_err_req_line_num,
l_err_req_dist_num,
l_err_req_line_id,
l_err_req_dist_id
FROM
po_line_locations_all plla,
po_distributions_all pda,
po_requisition_lines_all prla,
po_req_distributions_all prda
WHERE
plla.po_release_id = p_release_id
AND plla.line_location_id = p_po_change_table(s).document_line_location_id
AND pda.line_location_id = plla.line_location_id
AND pda.po_distribution_id = p_po_change_table(s).document_distribution_id
AND pda.req_distribution_id = prda.distribution_id
AND prda.requisition_line_id = prla.requisition_line_id;
SELECT
segment1,
type_lookup_code
INTO
l_po_num,
l_type_lookup_code
FROM po_headers_all
WHERE po_header_id = p_header_id;
SELECT
pha.segment1,
pra.release_num,
pha.type_lookup_code
INTO
l_blanket_num,
l_release_num,
l_type_lookup_code
FROM po_headers_all pha,
po_releases_all pra
WHERE pra.po_release_id = p_release_id
AND pra.po_header_id = pha.po_header_id;
Release BLANKET_NUM - RELEASE_NUM your selected currency.
Please contact your Purchasing department for assistance.*/
p_errortable.msg_data.extend(1);
--PO_RCO_DIST_NO_EXCH: No exchange rate conversion information is available for DOC_TYPE DOC_NUM line LINE_NUM for your selected currency. Please contact your Purchasing department for assistance.
p_errortable.msg_data.extend(1);
SELECT currency_code
INTO l_transaction_currency
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id ;
SELECT currency_code, fsp.set_of_books_id
INTO l_functional_currency, l_set_of_books_id
FROM
gl_sets_of_books gsob,
financials_system_parameters fsp
WHERE fsp.set_of_books_id = gsob.set_of_books_id;
SELECT nvl(rate_type,' ')
INTO l_rate_type
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id;
SELECT rate_date
INTO l_conversion_date
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id;
SELECT rate
INTO l_rate
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id;
SELECT quantity,
ship_to_location_id,
ship_to_organization_id,
need_by_date
INTO l_ship_quantity,
l_ship_to_location_id,
l_ship_to_organization_id,
l_need_by_date
FROM po_line_locations_all
WHERE line_location_id = p_po_change_table(i).document_line_location_id;
SELECT
plla.po_header_id,
plla.po_release_id
INTO
l_current_hdr_id,
l_current_rel_id
FROM
po_line_locations_all plla,
po_requisition_lines_all prla
WHERE prla.line_location_id = plla.line_location_id
AND prla.requisition_line_id = p_req_change_table(l_current_row_id).document_line_id;
l_po_change_table.delete;
SELECT
plla.po_header_id,
plla.po_release_id
INTO
l_hdr_id,
l_rel_id
FROM
po_line_locations_all plla,
po_requisition_lines_all prla
WHERE prla.line_location_id = plla.line_location_id
AND prla.requisition_line_id = p_req_change_table(i).document_line_id;
SELECT org_id
INTO l_req_org_id
FROM po_requisition_headers_all
WHERE requisition_header_id = p_req_hdr_id;
*Simple API to insert change records into PO_CHANGE_REQUESTS table
*/
PROCEDURE insert_reqchange(p_change_table change_tbl_type,
p_chn_req_grp_id NUMBER)
IS
l_api_name VARCHAR2(50) := 'Insert_ReqChange';
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,
created_by,
creation_date,
document_line_id,
document_line_number,
document_distribution_id,
document_distribution_number,
old_quantity,
new_quantity,
old_price,
new_price,
old_need_by_date,
new_need_by_date,
old_currency_unit_price,
new_currency_unit_price,
last_updated_by,
last_update_date,
last_update_login,
requester_id,
change_active_flag,
ref_po_header_id,
ref_po_num,
ref_po_release_id,
ref_po_rel_num,
old_start_date,
new_start_date,
old_expiration_date,
new_expiration_date,
old_amount,
new_amount,
old_currency_amount,
new_currency_amount
)
VALUES
(
p_chn_req_grp_id,
po_chg_request_seq.nextval,
'REQUESTER',
p_change_table(i).action_type,
p_change_table(i).request_reason,
p_change_table(i).request_level,
p_change_table(i).request_status,
'REQ',
p_change_table(i).document_header_id,
p_change_table(i).document_num,
p_change_table(i).document_revision_num,
l_req_user_id,
SYSDATE,
p_change_table(i).document_line_id,
p_change_table(i).document_line_number,
p_change_table(i).document_distribution_id,
p_change_table(i).document_distribution_number,
p_change_table(i).old_quantity,
p_change_table(i).new_quantity,
p_change_table(i).old_price,
p_change_table(i).new_price,
p_change_table(i).old_date,
p_change_table(i).new_date,
p_change_table(i).old_currency_unit_price,
p_change_table(i).new_currency_unit_price,
l_req_user_id,
SYSDATE,
l_req_user_id,
p_change_table(i).requester_id,
'Y',
p_change_table(i).referenced_po_header_id,
p_change_table(i).referenced_po_document_num,
p_change_table(i).referenced_release_id,
p_change_table(i).referenced_release_num,
p_change_table(i).old_start_date,
p_change_table(i).new_start_date,
p_change_table(i).old_end_date,
p_change_table(i).new_end_date,
p_change_table(i).old_budget_amount,
p_change_table(i).new_budget_amount,
p_change_table(i).old_currency_budget_amount,
p_change_table(i).new_currency_budget_amount
);
END insert_reqchange;
SELECT
decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount, prl.unit_price * prd.req_line_quantity),
decode(prl.matching_basis, 'AMOUNT', p_dist_amount, nvl(p_price, prl.unit_price) * nvl(p_quantity, prd.req_line_quantity)),
prd.recoverable_tax,
prd.nonrecoverable_tax
INTO
l_dist_total,
l_new_total,
l_rec_tax,
l_nonrec_tax
FROM
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prd.distribution_id = p_dist_id
AND prd.requisition_line_id = prl.requisition_line_id;
*Given a change group ID, update all distribution records
*with tax information, if needed
+-------------------------------------------------------*/
PROCEDURE update_recordswithtax(p_chn_req_grp_id NUMBER)
IS
l_api_name VARCHAR2(50) := 'Update_RecordsWithTax';
SELECT
change_request_id,
new_quantity,
document_distribution_id
FROM po_change_requests
WHERE change_request_group_id = grp_id
AND document_line_id = line_id
AND request_level = 'DISTRIBUTION';
SELECT DISTINCT document_line_id
FROM po_change_requests
WHERE change_request_group_id = grp_id
AND new_quantity IS NOT NULL;
SELECT
change_request_id,
document_line_id,
new_price
FROM po_change_requests
WHERE change_request_group_id = grp_id
AND new_price IS NOT NULL;
SELECT
distribution_id,
req_line_quantity
FROM po_req_distributions_all
WHERE requisition_line_id = l_line_id;
SELECT
change_request_id,
recoverable_tax,
nonrecoverable_tax
FROM po_change_requests
WHERE change_request_group_id = p_chn_req_grp_id
AND document_distribution_id = l_dist_id;
SELECT nvl(new_currency_unit_price, new_price)
INTO l_price
FROM po_change_requests
WHERE change_request_group_id = p_chn_req_grp_id
AND document_line_id = l_line_id
AND request_level = 'LINE'
AND new_price IS NOT NULL;
SELECT nvl(currency_unit_price, unit_price)
INTO l_price
FROM po_requisition_lines_all
WHERE requisition_line_id = l_line_id;
--INNER LOOP: After getting the most recent price, update child distribution records with tax information.
OPEN l_dist_with_chn_csr(p_chn_req_grp_id, l_line_id);
SELECT req_line_quantity
INTO l_quantity
FROM po_req_distributions_all
WHERE distribution_id = l_dist_id;
UPDATE po_change_requests
SET recoverable_tax = l_rec_tax,
nonrecoverable_tax = l_nonrec_tax
WHERE change_request_id = l_id;
--2nd OUTER LOOP: update recoverable and non recoverable tax attributes of Line Records (with Price Change)
OPEN l_line_id_with_price_chn_csr(p_chn_req_grp_id);
UPDATE po_change_requests
SET recoverable_tax = l_rec_tax,
nonrecoverable_tax = l_nonrec_tax
WHERE change_request_id = l_id;
END update_recordswithtax;
*Given a change group ID, update all line records
*with tax information, if qunatity is changed needed
+-------------------------------------------------------*/
PROCEDURE update_internalrecordswithtax(p_chn_req_grp_id NUMBER)
IS
l_id NUMBER;
SELECT DISTINCT document_line_id
FROM po_change_requests
WHERE change_request_group_id = grp_id
AND new_quantity IS NOT NULL;
SELECT
distribution_id,
req_line_quantity
FROM po_req_distributions_all
WHERE requisition_line_id = l_line_id;
SELECT
change_request_id,
recoverable_tax,
nonrecoverable_tax
FROM po_change_requests
WHERE change_request_group_id = p_chn_req_grp_id
AND document_distribution_id = l_dist_id;
SELECT
change_request_id,
new_quantity,
document_distribution_id
FROM po_change_requests
WHERE change_request_group_id = grp_id
AND document_line_id = line_id
AND request_level = 'LINE';
l_api_name CONSTANT VARCHAR(30) := 'Update_InternalRecordsWithTax';
SELECT nvl(currency_unit_price, unit_price)
INTO l_price
FROM po_requisition_lines_all
WHERE requisition_line_id = l_line_id;
--INNER LOOP: After getting the most recent price, update child distribution records with tax information.
OPEN l_dist_with_chn_csr(p_chn_req_grp_id, l_line_id);
UPDATE po_change_requests
SET recoverable_tax = l_rec_tax,
nonrecoverable_tax = l_nonrec_tax
WHERE change_request_id = l_id;
END update_internalrecordswithtax;
SELECT
'PO',
pha.segment1,
pha.revision_num,
pla.po_line_id,
pla.line_num,
plla.line_location_id,
plla.shipment_num,
pda.po_distribution_id,
pda.distribution_num,
prha.segment1,
prla.line_num,
prda.distribution_num,
prda.req_line_quantity,
pda.quantity_ordered,
prha.preparer_id,
plla.ship_to_location_id,
plla.ship_to_organization_id,
prla.unit_meas_lookup_code,
nvl(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
prla.item_id,
pha.rate,
prla.unit_price,
prla.need_by_date,
prla.assignment_start_date,
prla.assignment_end_date,
prda.req_line_amount,
prda.req_line_currency_amount,
pda.amount_ordered
INTO
l_po_doc_type,
l_po_num,
l_po_revision_num,
l_po_line_id,
l_po_line_number,
l_po_line_location_id,
l_po_shipment_number,
l_po_distribution_id,
l_po_distribution_number,
l_req_num,
l_req_line_number,
l_req_dist_number,
l_old_req_quantity,
l_old_po_quantity,
l_preparer_id,
l_po_ship_to_loc_id,
l_po_ship_to_org_id,
l_req_uom,
l_po_uom,
l_item_id,
l_po_to_req_rate,
l_old_req_price,
l_old_req_date,
l_old_start_date,
l_old_end_date,
l_old_amount,
l_old_curr_amount,
l_old_po_amount
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_req_distributions_all prda,
po_requisition_lines_all prla,
po_requisition_headers_all prha,
po_distributions_all pda
WHERE
prda.distribution_id = req_change_record_tbl(req_index).document_distribution_id
AND prda.requisition_line_id = prla.requisition_line_id
AND prla.line_location_id = plla.line_location_id
AND plla.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
AND prha.requisition_header_id = prla.requisition_header_id
AND pda.req_distribution_id = prda.distribution_id
AND pda.line_location_id = prla.line_location_id;
SELECT
'PO',
pha.segment1,
pha.revision_num,
pla.po_line_id,
pla.line_num,
plla.line_location_id,
plla.shipment_num,
prha.segment1,
prla.line_num,
pla.unit_price,
prla.currency_unit_price,
prla.unit_price,
prla.need_by_date,
plla.need_by_date,
prha.preparer_id,
plla.ship_to_location_id,
plla.ship_to_organization_id,
prla.unit_meas_lookup_code,
nvl(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
prla.item_id,
pha.rate,
prla.quantity,
plla.quantity,
prla.assignment_start_date,
prla.assignment_end_date,
prla.amount,
prla.currency_amount,
plla.amount
INTO
l_po_doc_type,
l_po_num,
l_po_revision_num,
l_po_line_id,
l_po_line_number,
l_po_line_location_id,
l_po_shipment_number,
l_req_num,
l_req_line_number,
l_old_po_price,
l_old_curr_unit_price,
l_old_req_price,
l_old_req_date,
l_old_po_date,
l_preparer_id,
l_po_ship_to_loc_id,
l_po_ship_to_org_id,
l_req_uom,
l_po_uom,
l_item_id,
l_po_to_req_rate,
l_old_req_quantity,
l_old_po_quantity,
l_old_start_date,
l_old_end_date,
l_old_amount,
l_old_curr_amount,
l_old_po_amount
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE
prla.requisition_line_id = req_change_record_tbl(req_index).document_line_id
AND prla.line_location_id = plla.line_location_id
AND plla.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
AND prha.requisition_header_id = prla.requisition_header_id;
SELECT
'PO',
pha.segment1,
pra.revision_num,
plla.po_line_id,
plla.line_location_id,
plla.shipment_num,
pda.po_distribution_id,
pda.distribution_num,
prha.segment1,
prla.line_num,
prda.distribution_num,
prda.req_line_quantity,
pda.quantity_ordered,
prha.preparer_id,
plla.ship_to_location_id,
plla.ship_to_organization_id,
prla.unit_meas_lookup_code,
nvl(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
prla.item_id,
pha.rate,
prla.unit_price,
prla.need_by_date,
pra.release_num,
prla.assignment_start_date,
prla.assignment_end_date,
prda.req_line_amount,
prda.req_line_currency_amount,
pda.amount_ordered
INTO
l_po_doc_type,
l_po_num,
l_po_revision_num,
l_po_line_id,
l_po_line_location_id,
l_po_shipment_number,
l_po_distribution_id,
l_po_distribution_number,
l_req_num,
l_req_line_number,
l_req_dist_number,
l_old_req_quantity,
l_old_po_quantity,
l_preparer_id,
l_po_ship_to_loc_id,
l_po_ship_to_org_id,
l_req_uom,
l_po_uom,
l_item_id,
l_po_to_req_rate,
l_old_req_price,
l_old_req_date,
l_release_num,
l_old_start_date,
l_old_end_date,
l_old_amount,
l_old_curr_amount,
l_old_po_amount
FROM
po_headers_all pha,
po_releases_all pra,
po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda,
po_req_distributions_all prda,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE
prda.distribution_id = req_change_record_tbl(req_index).document_distribution_id
AND prda.distribution_id = pda.req_distribution_id
AND pda.line_location_id = plla.line_location_id
AND plla.po_release_id = pra.po_release_id
AND pra.po_header_id = pha.po_header_id
AND prla.requisition_line_id = req_change_record_tbl(req_index).document_line_id
AND prha.requisition_header_id = prla.requisition_header_id
AND prla.line_location_id = pda.line_location_id
AND pla.po_line_id = plla.po_line_id;
SELECT
'PO',
pha.segment1,
pra.revision_num,
plla.po_line_id,
plla.line_location_id,
plla.shipment_num,
prha.segment1,
prla.line_num,
plla.price_override,
prla.currency_unit_price,
prla.unit_price,
prla.need_by_date,
plla.need_by_date,
prha.preparer_id,
plla.ship_to_location_id,
plla.ship_to_organization_id,
prla.unit_meas_lookup_code,
nvl(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
prla.item_id,
pha.rate,
prla.quantity,
plla.quantity,
pra.release_num,
prla.assignment_start_date,
prla.assignment_end_date,
prla.amount,
prla.currency_amount,
plla.amount
INTO
l_po_doc_type,
l_po_num,
l_po_revision_num,
l_po_line_id,
l_po_line_location_id,
l_po_shipment_number,
l_req_num,
l_req_line_number,
l_old_po_price,
l_old_curr_unit_price,
l_old_req_price,
l_old_req_date,
l_old_po_date,
l_preparer_id,
l_po_ship_to_loc_id,
l_po_ship_to_org_id,
l_req_uom,
l_po_uom,
l_item_id,
l_po_to_req_rate,
l_old_req_quantity,
l_old_po_quantity,
l_release_num,
l_old_start_date,
l_old_end_date,
l_old_amount,
l_old_curr_amount,
l_old_po_amount
FROM
po_headers_all pha,
po_releases_all pra,
po_lines_all pla,
po_line_locations_all plla,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE
prla.requisition_line_id = req_change_record_tbl(req_index).document_line_id
AND prla.line_location_id = plla.line_location_id
AND plla.po_release_id = pra.po_release_id
AND pra.po_header_id = pha.po_header_id
AND prha.requisition_header_id = prla.requisition_header_id
AND pla.po_line_id = plla.po_line_id;
SELECT po_header_id INTO l_header_id
FROM po_line_locations_all
WHERE line_location_id = p_line_loc_id;
SELECT
prla.source_type_code,
prla.auction_display_number,
prla.auction_line_number,
prla.reqs_in_pool_flag,
prla.line_location_id,
prha.change_pending_flag,
nvl(prla.modified_by_agent_flag, 'N'),
prha.transferred_to_oe_flag,
nvl(prla.cancel_flag,'N')
INTO
l_source_type_code,
l_auction_display_number,
l_auction_line_number,
l_reqs_in_pool_flag,
l_po_line_loc_id,
l_req_change_pending_flag,
l_modified_by_agent,
l_transferred_to_oe_flag,
l_cancelled
FROM
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE
prla.requisition_line_id = p_req_line_id AND
prla.requisition_header_id = prha.requisition_header_id;
SELECT
prla.line_location_id,
prha.change_pending_flag,
nvl(prla.modified_by_agent_flag, 'N')
INTO
l_po_line_loc_id,
l_req_change_pending_flag,
l_modified_by_agent
FROM
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE
prla.requisition_line_id = p_req_line_id AND
prla.requisition_header_id = prha.requisition_header_id;
SELECT COUNT(DISTINCT nvl(prda.requisition_line_id, - 1))
INTO l_count
FROM
po_req_distributions_all prda,
po_distributions_all pda,
po_requisition_lines_all prla
WHERE pda.line_location_id = prla.line_location_id
AND prla.requisition_line_id = p_req_line_id
AND pda.req_distribution_id = prda.distribution_id(+ );
SELECT
pha.po_header_id,
pla.po_line_id,
plla.po_release_id,
plla.line_location_id,
pha.agent_id,
prla.quantity - nvl(prla.quantity_cancelled, 0),
plla.quantity_received,
plla.quantity_billed,
prla.amount,
plla.amount_received,
plla.amount_billed,
plla.receipt_required_flag
INTO
l_po_header_id,
l_po_line_id,
l_po_release_id,
l_po_line_loc_id,
l_agent_id,
l_quantity,
l_received_quantity,
l_billed_quantity,
l_amount,
l_received_amount,
l_billed_amount,
l_receipt_required_flag
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_requisition_lines_all prla
WHERE
prla.requisition_line_id = p_req_line_id
AND prla.line_location_id = plla.line_location_id
AND plla.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id;
SELECT 1
INTO l_rcv_transaction_exist
FROM rcv_transactions_interface
WHERE
processing_status_code = 'PENDING' AND
po_line_location_id = l_po_line_loc_id;
SELECT 1
INTO l_asn_exist
FROM rcv_shipment_lines
WHERE
po_line_location_id = l_po_line_loc_id AND
nvl(quantity_shipped, 0) > nvl(quantity_received, 0) AND
nvl(asn_line_flag, 'N') = 'Y' AND
nvl(shipment_line_status_code, 'EXPECTED') <> 'CANCELLED';
SELECT 1
INTO l_not_delivered
FROM po_line_locations_all plla
WHERE
plla.line_location_id = l_po_line_loc_id AND
((nvl(plla.quantity_received, 0) >
(SELECT SUM(nvl(pod.quantity_delivered, 0))
FROM po_distributions pod
WHERE pod.line_location_id = plla.line_location_id)) OR
(nvl(plla.amount_received, 0) >
(SELECT SUM(nvl(pod.amount_delivered, 0))
FROM po_distributions pod
WHERE pod.line_location_id = plla.line_location_id)));
SELECT 1
INTO l_dist_not_valid
FROM
po_line_locations_all poll,
po_distributions_all pod,
gl_code_combinations gcc
WHERE
poll.line_location_id = l_po_line_loc_id AND
pod.line_location_id = poll.line_location_id AND
gcc.code_combination_id = pod.code_combination_id AND
((trunc(SYSDATE) NOT BETWEEN
nvl(gcc.start_date_active, trunc(SYSDATE) - 1) AND
nvl(gcc.end_date_active, trunc(SYSDATE) + 1)
) OR
pod.quantity_billed > pod.quantity_ordered OR -- fully billed
pod.quantity_delivered > pod.quantity_ordered OR -- over delivered
(pod.quantity_ordered >= pod.quantity_billed AND -- over billed
nvl(poll.receipt_required_flag, 'Y') <> 'N' AND
pod.quantity_billed > pod.quantity_delivered) OR
pod.amount_billed > pod.amount_ordered OR
pod.amount_delivered > pod.amount_ordered OR
(pod.amount_ordered >= pod.amount_billed AND
nvl(poll.receipt_required_flag, 'Y') <> 'N' AND
pod.amount_billed > pod.amount_delivered));
SELECT org_id
INTO l_po_org_id
FROM po_headers_all
WHERE po_header_id = l_po_header_id;
SELECT agent_id, org_id
INTO l_agent_id, l_po_org_id
FROM po_releases_all
WHERE po_release_id = l_po_release_id;
SELECT
prla.purchase_basis,
prla.line_location_id,
prla.source_type_code,
prla.auction_display_number,
prla.auction_line_number,
prla.reqs_in_pool_flag,
nvl(prla.catalog_type,' '),
prla.noncat_template_id,
prla.destination_type_code,
prha.transferred_to_oe_flag
INTO
l_line_type,
l_line_loc_id,
l_source_type_code,
l_auction_display_number,
l_auction_line_number,
l_reqs_in_pool_flag,
l_catalog_type,
l_template_id,
l_destination_type_code,
l_transferred_to_oe_flag
FROM
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE
prla.requisition_line_id = p_req_line_id AND
prla.requisition_header_id = prha.requisition_header_id;
SELECT price_editable_flag, amount_editable_flag
INTO l_price_editable_flag, l_amount_editable_flag
FROM por_noncat_templates_all_b
WHERE template_id = l_template_id;
SELECT order_type_lookup_code
INTO l_order_type_lookup_code
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id;
SELECT pha.global_agreement_flag, pha.po_header_id
INTO l_global_agreement_flag, l_po_header_id
FROM po_headers_all pha,
po_requisition_lines_all prla
WHERE pha.po_header_id (+ ) = prla.blanket_po_header_id
AND prla.requisition_line_id = p_req_line_id;
SELECT pla.allow_price_override_flag
INTO l_allow_price_override_flag
FROM po_requisition_lines_all prl,
po_headers_all pha,
po_lines_all pla
WHERE pha.po_header_id = l_po_header_id
AND pla.po_header_id = pha.po_header_id
AND prl.blanket_po_line_num = pla.line_num
AND prl.requisition_line_id = p_req_line_id;
SELECT po_release_id INTO l_release_id
FROM po_line_locations_all
WHERE line_location_id = l_line_loc_id;
SELECT
nvl(pha.authorization_status,' '),
nvl(plla.closed_code, 'OPEN'),
nvl(plla.cancel_flag, 'N'),
pla.allow_price_override_flag,
plla.accrue_on_receipt_flag,
plla.quantity_received,
plla.quantity_billed,
nvl(pha.frozen_flag, 'N')
INTO
l_authorization_status ,
l_closed_code,
l_cancel_flag,
l_allow_price_override_flag,
l_accrue_on_receipt_flag,
l_qty_received,
l_qty_billed,
l_frozen_flag
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_requisition_lines_all prla
WHERE pha.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND plla.line_location_id = prla.line_location_id
AND prla.requisition_line_id = p_req_line_id;
SELECT
nvl(pra.authorization_status,' '),
nvl(plla.closed_code, 'OPEN'),
nvl(plla.cancel_flag, 'N'),
pla.allow_price_override_flag,
plla.accrue_on_receipt_flag,
plla.quantity_received,
plla.quantity_billed,
nvl(pra.frozen_flag,' ')
INTO
l_authorization_status ,
l_closed_code,
l_cancel_flag,
l_allow_price_override_flag,
l_accrue_on_receipt_flag,
l_qty_received,
l_qty_billed,
l_frozen_flag
FROM
po_lines_all pla,
po_line_locations_all plla,
po_requisition_lines_all prla,
po_releases_all pra
WHERE pla.po_line_id = plla.po_line_id
AND plla.line_location_id = prla.line_location_id
AND prla.requisition_line_id = p_req_line_id
AND pra.po_release_id = plla.po_release_id;
SELECT COUNT(DISTINCT nvl(prda.requisition_line_id, - 1))
INTO l_count
FROM
po_req_distributions_all prda,
po_distributions_all pda,
po_requisition_lines_all prla
WHERE pda.line_location_id = prla.line_location_id
AND prla.requisition_line_id = p_req_line_id
AND pda.req_distribution_id = prda.distribution_id(+ );
SELECT COUNT(1)
INTO l_count
FROM
po_requisition_lines_all prla,
po_line_locations_all plla,
po_line_locations_all plla2
WHERE plla.line_location_id = prla.line_location_id
AND prla.requisition_line_id = p_req_line_id
AND plla2.po_line_id = plla.po_line_id;
DELETE FROM po_change_requests
WHERE document_header_id = p_req_hdr_id
AND request_status = 'SYSTEMSAVE'
AND initiator = 'REQUESTER';
SELECT distribution_id
INTO l_req_dist_id
FROM po_req_distributions_all
WHERE requisition_line_id = p_change_table.req_line_id(x);
SELECT distribution_id
INTO l_req_dist_id
FROM po_req_distributions_all
WHERE requisition_line_id = p_change_table.req_line_id(x);
SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
insert_reqchange(l_req_change_table, p_change_request_group_id);
update_recordswithtax(p_change_request_group_id);
insert_linequantityoramount(p_change_request_group_id);
insert_pricebreakrows(p_change_request_group_id);
**simply insert records into PO_CHANGE_REQUESTS table
---------------------------------------------------------------*/
PROCEDURE save_reqcancel(p_api_version IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_req_hdr_id IN NUMBER,
p_cancel_table IN po_req_cancel_table,
p_change_request_group_id OUT NOCOPY NUMBER,
x_retmsg OUT NOCOPY VARCHAR2,
p_grp_id IN NUMBER)
IS
l_api_name VARCHAR2(50) := 'Save_ReqCancel';
SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
DELETE FROM po_change_requests
WHERE document_header_id = p_req_hdr_id
AND initiator = 'REQUESTER'
AND request_status = 'SYSTEMSAVE';
SELECT po_chg_request_seq.nextval INTO l_chn_req_id FROM dual;
SELECT
prha.segment1,
prla.line_num,
prla.line_location_id,
prha.preparer_id,
prla.unit_price,
prla.quantity,
prla.need_by_date,
prla.currency_unit_price
INTO
l_req_num,
l_req_line_num,
l_line_loc_id,
l_preparer_id,
l_req_price,
l_req_quantity,
l_req_date,
l_req_currency_price
FROM
po_requisition_headers_all prha,
po_requisition_lines_all prla
WHERE prla.requisition_line_id = p_cancel_table.req_line_id(i)
AND prla.requisition_header_id = prha.requisition_header_id;
SELECT
po_release_id,
po_header_id
INTO
l_po_release_id,
l_po_header_id
FROM po_line_locations_all
WHERE line_location_id = l_line_loc_id;
SELECT revision_num, segment1 INTO
l_po_revision_num, l_po_num
FROM po_headers_all
WHERE po_header_id = l_po_header_id;
SELECT segment1 INTO l_po_num
FROM po_headers_all
WHERE po_header_id = l_po_header_id;
SELECT revision_num, release_num
INTO l_po_revision_num, l_po_release_num
FROM po_releases_all
WHERE po_release_id = l_po_release_id;
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,
created_by,
creation_date,
document_line_id,
document_line_number,
last_updated_by,
last_update_date,
last_update_login,
requester_id,
change_active_flag,
old_price,
old_quantity,
old_need_by_date,
old_currency_unit_price,
ref_po_header_id,
ref_po_num,
ref_po_release_id,
ref_po_rel_num )
VALUES
(
p_change_request_group_id,
l_chn_req_id,
'REQUESTER',
'CANCELLATION',
p_cancel_table.change_reason(i),
'LINE',
'SYSTEMSAVE',
'REQ',
p_req_hdr_id,
l_req_num,
l_po_revision_num,
l_req_user_id,
SYSDATE,
p_cancel_table.req_line_id(i),
l_req_line_num,
l_req_user_id,
SYSDATE,
l_req_user_id,
l_preparer_id,
'Y',
l_req_price,
l_req_quantity,
l_req_date,
l_req_currency_price,
l_po_header_id,
l_po_num,
l_po_release_id,
l_po_release_num
);
SELECT
document_header_id,
document_line_id,
document_distribution_id,
new_quantity,
new_price,
new_need_by_date,
request_reason
FROM po_change_requests
WHERE change_request_group_id = grp_id
AND action_type = 'MODIFICATION';
SELECT
document_header_id,
document_line_id,
request_reason
FROM po_change_requests
WHERE change_request_group_id = grp_id
AND action_type = 'CANCELLATION';
SELECT
document_line_id line_id,
document_distribution_id dist_id,
document_header_id hdr_id,
document_num req_num
FROM
po_change_requests
WHERE
change_request_group_id = grp_id AND
(new_quantity IS NOT NULL OR new_amount IS NOT NULL) AND
action_type = 'MODIFICATION'
UNION
SELECT
prda.requisition_line_id line_id,
prda.distribution_id dist_id,
prla.requisition_header_id hdr_id,
prha.segment1 req_num
FROM
po_req_distributions_all prda,
po_requisition_lines_all prla,
po_change_requests pcr,
po_requisition_headers_all prha
WHERE
prha.requisition_header_id = prla.requisition_header_id AND
prla.requisition_line_id = prda.requisition_line_id AND
pcr.document_line_id = prla.requisition_line_id AND
pcr.change_request_group_id = grp_id AND
pcr.action_type = 'MODIFICATION' AND (pcr.new_price IS NOT NULL OR
pcr.new_need_by_date IS NOT NULL);
SELECT -- any quantity or amount change
pda.po_distribution_id
FROM
po_change_requests pcr,
po_req_distributions_all prda,
po_distributions_all pda,
po_headers_all pha
WHERE
pcr.change_request_group_id = grp_id AND
(pcr.new_quantity IS NOT NULL OR pcr.new_amount IS NOT NULL) AND
pcr.action_type = 'MODIFICATION' AND
pcr.document_distribution_id = prda.distribution_id AND
prda.distribution_id = pda.req_distribution_id AND
pda.po_header_id = pha.po_header_id AND
pha.type_lookup_code = 'STANDARD'
UNION
SELECT -- select distributions that are effected with any line change
pda.po_distribution_id
FROM
po_change_requests pcr,
po_requisition_lines_all prla,
po_req_distributions_all prda,
po_distributions_all pda,
po_headers_all pha
WHERE
pcr.change_request_group_id = grp_id AND
pcr.action_type = 'MODIFICATION' AND
(pcr.new_price IS NOT NULL OR pcr.new_need_by_date IS NOT NULL) AND
pcr.document_line_id = prla.requisition_line_id AND
prla.requisition_line_id = prda.requisition_line_id AND
prda.distribution_id = pda.req_distribution_id AND
pda.po_header_id = pha.po_header_id AND
pha.type_lookup_code = 'STANDARD';
SELECT -- any quantity or amount change
pda.po_distribution_id
FROM
po_change_requests pcr,
po_req_distributions_all prda,
po_distributions_all pda,
po_requisition_lines_all prla,
po_line_locations_all plla
WHERE
pcr.change_request_group_id = grp_id AND
(pcr.new_quantity IS NOT NULL OR pcr.new_amount IS NOT NULL) AND
pcr.action_type = 'MODIFICATION' AND
pcr.document_distribution_id = prda.distribution_id AND
prda.distribution_id = pda.req_distribution_id AND
prla.requisition_line_id = prda.requisition_line_id AND
prla.line_location_id = plla.line_location_id AND
plla.po_release_id IS NOT NULL
UNION -- select distributions that are effected with any line change
SELECT
pda.po_distribution_id
FROM
po_change_requests pcr,
po_requisition_lines_all prla,
po_req_distributions_all prda,
po_distributions_all pda,
po_line_locations_all plla
WHERE
pcr.change_request_group_id = grp_id AND
pcr.action_type = 'MODIFICATION' AND
(pcr.new_price IS NOT NULL OR pcr.new_need_by_date IS NOT NULL) AND
pcr.document_line_id = prla.requisition_line_id AND
prla.requisition_line_id = prda.requisition_line_id AND
prda.distribution_id = pda.req_distribution_id AND
prla.line_location_id = plla.line_location_id AND
plla.po_release_id IS NOT NULL;
SELECT
nvl(fsp.req_encumbrance_flag, 'N'),
nvl(fsp.purch_encumbrance_flag, 'N')
INTO
l_flag_one,
l_flag_two
FROM financials_system_parameters fsp;
l_distribution_id_tbl.delete;
SELECT
plla.line_location_id,
pda.po_distribution_id,
plla.po_line_id,
nvl(plla.price_override, pla.unit_price),
pda.quantity_ordered,
pda.amount_ordered,
prla.item_id,
prla.unit_meas_lookup_code,
nvl(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
pha.rate,
plla.need_by_date,
plla.ship_to_organization_id,
plla.ship_to_location_id
INTO
l_shipment_id,
l_distribution_id,
l_line_id,
l_old_price,
l_old_quantity,
l_old_amount,
l_item_id,
l_req_uom,
l_po_uom,
l_po_to_req_rate,
l_old_need_by_date,
l_ship_to_org_id,
l_ship_to_loc_id
FROM
po_req_distributions_all prda,
po_requisition_lines_all prla,
po_line_locations_all plla,
po_distributions_all pda,
po_lines_all pla,
po_headers_all pha
WHERE
prda.distribution_id = l_req_dist_id
AND prda.requisition_line_id = prla.requisition_line_id
AND pda.req_distribution_id = prda.distribution_id
AND pda.line_location_id = prla.line_location_id
AND plla.line_location_id = prla.line_location_id
AND plla.po_header_id = plla.po_header_id
AND plla.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id;
SELECT new_amount
INTO l_new_amount
FROM po_change_requests
WHERE
change_request_group_id = p_group_id AND
document_distribution_id = l_req_dist_id AND
new_amount IS NOT NULL;
SELECT new_quantity
INTO l_new_quantity
FROM po_change_requests
WHERE change_request_group_id = p_group_id
AND document_distribution_id = l_req_dist_id
AND new_quantity IS NOT NULL;
SELECT new_price
INTO l_new_price
FROM po_change_requests
WHERE change_request_group_id = p_group_id
AND document_line_id = l_req_line_id
AND new_price IS NOT NULL;
SELECT new_need_by_date
INTO l_new_need_by_date
FROM po_change_requests
WHERE change_request_group_id = p_group_id
AND document_line_id = l_req_line_id
AND new_need_by_date IS NOT NULL;
UPDATE po_encumbrance_gt
SET
amount_ordered = l_new_amount,
quantity_ordered = l_new_quantity,
price = l_new_price,
nonrecoverable_tax = l_new_tax
WHERE
distribution_id = l_distribution_id AND
adjustment_status = po_document_funds_grp.g_adjustment_status_new;
SELECT
prda.distribution_id,
prda.distribution_num,
prda.requisition_line_id,
prla.line_num
INTO
l_fc_req_distr_id,
l_fc_req_distr_num,
l_fc_req_line_id,
l_fc_req_line_num
FROM
po_requisition_lines_all prla,
po_req_distributions_all prda,
po_distributions_all pda
WHERE
pda.po_distribution_id = l_fc_out_tbl.distribution_id(x)
AND pda.req_distribution_id = prda.distribution_id
AND prla.requisition_line_id = prda.requisition_line_id;
* If all requests are valid, update status to "NEW" and kick off workflow
*/
IF(x_errtable.req_line_id.count = 0) THEN
UPDATE po_change_requests
SET request_status = 'NEW'
WHERE change_request_group_id = p_group_id
AND request_status = 'SYSTEMSAVE';
SELECT
pcr.document_header_id,
pcr.document_line_id,
prla.line_location_id,
pcr.change_request_id
FROM
po_change_requests pcr,
po_requisition_lines_all prla
WHERE pcr.action_type = 'CANCELLATION'
AND pcr.change_request_group_id = grp_id
AND pcr.document_line_id = prla.requisition_line_id;
UPDATE po_change_requests
SET request_status = 'ACCEPTED'
WHERE change_request_id = l_chn_req_id;
--If all requests are valid, update status to "NEW", and kick off Workflow
IF(p_errtable.req_line_id.count = 0) THEN
UPDATE po_change_requests
SET request_status = 'NEW'
WHERE change_request_group_id = p_group_id
AND request_status = 'SYSTEMSAVE';
SELECT
prl.requisition_line_id,
prh.requisition_header_id
INTO
l_req_line_id,
l_req_header_id
FROM po_requisition_lines prl,
po_requisition_headers_all prh --
WHERE prl.requisition_line_id = p_req_line_id AND
prh.requisition_header_id = prl.requisition_header_id AND
(NOT EXISTS
(SELECT 'so line is not cancelled'
FROM
po_requisition_lines PORL,
po_requisition_headers_all PORH, --
po_system_parameters POSP
WHERE
PORL.requisition_line_id = p_req_line_id AND
PORL.requisition_header_id = PORH.requisition_header_id AND
(OE_ORDER_IMPORT_INTEROP_PUB.Get_Open_Qty(posp.order_source_id, porh.requisition_header_id, porl.requisition_line_id))>0)
AND NOT EXISTS
(SELECT 'line in interface table'
FROM
oe_headers_iface_all SOHI,
po_system_parameters POSP
WHERE
SOHI.orig_sys_document_ref = to_char(PRH.requisition_header_id)
AND SOHI.order_source_id = POSP.order_source_id));
x_update_allowed BOOLEAN := FALSE;
, X_Update_Allowed =>x_update_allowed
, X_Cancel_Allowed =>x_cancel_allowed
, X_msg_count =>X_msg_count
, X_msg_data =>X_msg_data
, X_return_status =>x_return_status
);
SELECT
prl.requisition_line_id,
prh.requisition_header_id
INTO
l_req_line_id,
l_req_header_id
FROM po_requisition_lines prl,
po_requisition_headers_all prh --
WHERE prl.requisition_line_id = p_req_line_id AND
prh.requisition_header_id = prl.requisition_header_id ;
, X_Update_Allowed =>x_update_allowed
, X_Cancel_Allowed =>x_cancel_allowed
, X_msg_count =>X_msg_count
, X_msg_data =>X_msg_data
, X_return_status =>x_return_status
);
* This procedure returns whether an internal line can be updated
* with quantity and need by date or not
**/
PROCEDURE is_internal_line_changeable(p_api_version IN NUMBER
, X_Update_Allowed OUT NOCOPY VARCHAR2
, X_Cancel_Allowed OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, p_req_line_id IN NUMBER)
IS
l_req_line_id NUMBER := 0;
l_Update_Allowed boolean :=FALSE;
l_update_allowed := FALSE;
SELECT
prl.requisition_line_id,
prl.requisition_header_id
INTO
l_req_line_id,
l_req_header_id
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = p_req_line_id;
, X_Update_Allowed =>l_update_allowed
, X_Cancel_Allowed =>l_cancel_allowed
, X_msg_count =>X_msg_count
, X_msg_data =>X_msg_data
, X_return_status =>x_return_status
);
x_update_allowed := POR_UTIL_PKG.bool_to_varchar(l_update_allowed);
po_debug.debug_var(l_log_head, l_progress, 'x_update_allowed', x_update_allowed);
**simply insert records into PO_CHANGE_REQUESTS table
---------------------------------------------------------------*/
PROCEDURE save_ireqcancel(p_api_version IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_req_hdr_id IN NUMBER,
p_cancel_table IN po_req_cancel_table,
p_change_request_group_id OUT NOCOPY NUMBER,
l_progress OUT NOCOPY VARCHAR2,
p_grp_id IN NUMBER)
IS
l_api_name VARCHAR2(50) := 'Save_IReqCancel';
SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
DELETE FROM po_change_requests
WHERE document_header_id = p_req_hdr_id
AND initiator = 'REQUESTER'
AND request_status = 'SYSTEMSAVE';
po_debug.debug_var(l_log_head, l_progress,'In PO_CHANGE_REQUESTS records deleted=', SQL%rowcount);
po_debug.debug_var(l_log_head, l_progress,'In PO_CHANGE_REQUESTS records deleted=', SQL%rowcount);
SELECT po_chg_request_seq.nextval INTO l_chn_req_id FROM dual;
SELECT
prha.segment1,
prla.line_num,
prha.preparer_id,
prla.unit_price,
prla.quantity,
prla.need_by_date
INTO
l_req_num,
l_req_line_num,
l_preparer_id,
l_req_price,
l_req_quantity,
l_req_date
FROM
po_requisition_headers_all prha,
po_requisition_lines_all prla
WHERE prla.requisition_line_id = p_cancel_table.req_line_id(i)
AND prla.requisition_header_id = prha.requisition_header_id;
select
po_release_id,
po_header_id
into
l_po_release_id,
l_po_header_id
from po_line_locations_all
where line_location_id = l_line_loc_id;
select revision_num,segment1 into
l_po_revision_num, l_po_num
from po_headers_all
where po_header_id = l_po_header_id;
select segment1 into l_po_num
from po_headers_all
where po_header_id = l_po_header_id;
select revision_num, release_num
into l_po_revision_num, l_po_release_num
from po_releases_all
where po_release_id = l_po_release_id;
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,
created_by,
creation_date,
document_line_id,
document_line_number,
last_updated_by,
last_update_date,
last_update_login,
requester_id,
change_active_flag,
old_price,
old_quantity,
old_need_by_date
)
VALUES
(
p_change_request_group_id,
l_chn_req_id,
'REQUESTER',
'CANCELLATION',
p_cancel_table.change_reason(i),
'LINE',
'SYSTEMSAVE',
'REQ',
p_req_hdr_id,
l_req_num,
l_req_user_id,
SYSDATE,
p_cancel_table.req_line_id(i),
l_req_line_num,
l_req_user_id,
SYSDATE,
l_req_user_id,
l_preparer_id,
'Y',
l_req_price,
l_req_quantity,
l_req_date
);
DELETE FROM po_change_requests
WHERE document_header_id = p_req_hdr_id
AND request_status = 'SYSTEMSAVE'
AND initiator = 'REQUESTER';
po_debug.debug_var(l_log_head, l_progress,'NO of rows deleted from PO_CHANGE_REQUESTS', SQL%rowcount);
SELECT
prha.segment1,
prda.distribution_id,
prla.need_by_date,
prla.quantity,
prla.amount,
prha.preparer_id
INTO
l_req_num,
l_req_dist_number,
l_old_req_date,
l_old_req_quantity,
l_old_amount,
l_preparer_id
FROM
po_requisition_lines_all prla,
po_requisition_headers_all prha,
po_req_distributions_all prda
WHERE
prha.requisition_header_id = p_req_hdr_id
AND prha.requisition_header_id = prla.requisition_header_id
AND prla.requisition_line_id = p_change_table.req_line_id(x)
AND prda.requisition_line_id = prla.requisition_line_id;
SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
insert_reqchange(l_req_change_table, p_change_request_group_id);
update_internalrecordswithtax(p_change_request_group_id);
---Insert_LineQuantityOrAmount(p_change_request_group_id); not inserting derived record in po_change_request
SELECT
pcr.document_header_id,
pcr.document_line_id,
prla.line_location_id,
pcr.change_request_id
FROM
po_change_requests pcr,
po_requisition_lines_all prla
WHERE pcr.action_type = 'CANCELLATION'
AND pcr.change_request_group_id = grp_id
AND pcr.document_line_id = prla.requisition_line_id;
update po_change_requests
set request_status = 'ACCEPTED'
where change_request_id = l_chn_req_id;
--If all requests are valid, update status to "NEW", and kick off Workflow
IF(p_errtable.req_line_id.count = 0) THEN
UPDATE po_change_requests
SET request_status = 'NEW'
WHERE change_request_group_id = p_group_id
AND request_status = 'SYSTEMSAVE';
SELECT preparer_id into l_preparer_id
FROM po_requisition_headers_all
WHERE requisition_header_id = p_req_hdr_id;
PROCEDURE update_reqcancel_from_so(
p_req_hdr_id IN NUMBER
, p_req_line_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR req_lines_cursor(x_requisition_header_id NUMBER) IS
SELECT requisition_line_id
FROM po_requisition_lines_all
WHERE requisition_header_id = x_requisition_header_id
AND SOURCE_TYPE_CODE = 'INVENTORY';
l_log_head CONSTANT VARCHAR2(100) := c_log_head || 'update_reqcancel_from_so';
SELECT COUNT(*) INTO l_count FROM po_change_requests
WHERE request_status in ( 'NEW' , 'MGR_PRE_APP' , 'MGR_APP')
AND DOCUMENT_TYPE= 'REQ'
AND REQUEST_LEVEL= 'LINE'
AND DOCUMENT_LINE_ID=p_req_line_id;
SAVEPOINT update_reqcancel_from_so_sp;
SELECT nvl(sum(w.shipped_quantity),0)
INTO l_open_quantity
FROM oe_order_lines_all oel
,oe_order_headers_all oeh
,wsh_delivery_details w
,po_requisition_lines_all pol
WHERE
oel.header_id = oeh.header_id
AND oel.line_id = w.source_line_id
AND w.source_code = 'OE'
AND w.released_status = 'C'
AND oel.source_document_line_id=pol.requisition_line_id
AND oel.source_document_id=pol.requisition_header_id
AND oeh.source_document_type_id =10
AND pol.REQUISITION_LINE_ID=l_req_line_id;
SELECT QUANTITY, nvl(QUANTITY_DELIVERED,0)
INTO l_quantity, l_quantity_delivered
FROM po_requisition_lines_all
WHERE REQUISITION_LINE_ID=l_req_line_id;
po_reqchangerequestwf_pvt.update_reqline_quan_changes(
p_req_line_id => p_req_line_id,
p_delta_quantity=> l_delta_quantity,
x_return_status =>l_return_status);
SELECT nvl(sum(w.shipped_quantity),0)
INTO l_open_quantity
FROM oe_order_lines_all oel
,oe_order_headers_all oeh
,wsh_delivery_details w
,po_requisition_lines_all pol
WHERE
oel.header_id = oeh.header_id
AND oel.line_id = w.source_line_id
AND w.source_code = 'OE'
AND w.released_status = 'C'
AND oel.source_document_line_id=pol.requisition_line_id
AND oel.source_document_id=pol.requisition_header_id
AND oeh.source_document_type_id =10
AND pol.REQUISITION_LINE_ID=p_req_line_id;
SELECT QUANTITY, nvl(QUANTITY_DELIVERED,0)
INTO l_quantity, l_quantity_delivered
FROM po_requisition_lines_all
WHERE REQUISITION_LINE_ID=p_req_line_id;
po_reqchangerequestwf_pvt.update_reqline_quan_changes(
p_req_line_id => p_req_line_id,
p_delta_quantity=> l_delta_quantity,
x_return_status =>l_return_status);
ROLLBACK TO update_reqcancel_from_so_sp;
END update_reqcancel_from_so;
PROCEDURE update_reqchange_from_so(
p_req_line_id IN NUMBER
, p_delta_quantity IN NUMBER
, p_new_need_by_date IN DATE
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_bool_ret_sts BOOLEAN;
SELECT count(*) INTO l_count
FROM PO_change_requests
WHERE request_status in ( 'NEW' , 'MGR_PRE_APP' , 'MGR_APP')
AND DOCUMENT_TYPE= 'REQ'
AND REQUEST_LEVEL= 'LINE'
AND DOCUMENT_LINE_ID=p_req_line_id;
SAVEPOINT update_reqchange_from_so_s;
po_reqchangerequestwf_pvt.update_reqline_quan_changes(
p_req_line_id => p_req_line_id,
p_delta_quantity=> p_delta_quantity,
x_return_status =>l_return_status);
ROLLBACK TO update_reqchange_from_so_s;
po_reqchangerequestwf_pvt.update_req_line_date_changes(p_req_line_id=>p_req_line_id,
p_need_by_date=> p_new_need_by_date,
x_return_status =>l_return_status);
ROLLBACK TO update_reqchange_from_so_s;
END update_reqchange_from_so;
SELECT
document_header_id,
document_line_id,
document_distribution_id,
new_quantity,
new_need_by_date,
request_reason
FROM po_change_requests
WHERE change_request_group_id = grp_id
AND action_type = 'MODIFICATION';
SELECT
document_header_id,
document_line_id,
request_reason
FROM po_change_requests
WHERE change_request_group_id = grp_id
AND action_type = 'CANCELLATION';
SELECT
document_line_id line_id,
document_distribution_id dist_id,
document_header_id hdr_id,
document_num req_num
FROM
po_change_requests
WHERE
change_request_group_id = grp_id AND
new_quantity IS NOT NULL AND
action_type = 'MODIFICATION'
;/* UNION
SELECT
prda.requisition_line_id line_id,
prda.distribution_id dist_id,
prla.requisition_header_id hdr_id,
prha.segment1 req_num
FROM
po_req_distributions_all prda,
po_requisition_lines_all prla,
po_change_requests pcr,
po_requisition_headers_all prha
WHERE
prha.requisition_header_id = prla.requisition_header_id AND
prla.requisition_line_id = prda.requisition_line_id AND
pcr.document_line_id = prla.requisition_line_id AND
pcr.change_request_group_id = grp_id AND
pcr.action_type = 'MODIFICATION' AND
pcr.new_need_by_date IS NOT NULL;*/
SELECT -- any quantity change
pcr.document_distribution_id
FROM
po_change_requests pcr
WHERE
pcr.change_request_group_id = grp_id AND
pcr.new_quantity IS NOT NULL AND
pcr.action_type = 'MODIFICATION';
SELECT -- any quantity or amount change
prda.distribution_id
FROM
po_change_requests pcr,
po_req_distributions_all prda
WHERE
pcr.change_request_group_id = grp_id AND
pcr.new_quantity IS NOT NULL AND
pcr.action_type = 'MODIFICATION' AND
pcr.document_distribution_id = prda.distribution_id
; UNION
SELECT -- select distributions that are effected with any line change
prda.distribution_id
FROM
po_change_requests pcr,
po_requisition_lines_all prla,
po_req_distributions_all prda
WHERE
pcr.change_request_group_id = grp_id AND
pcr.action_type = 'MODIFICATION' AND
pcr.new_need_by_date IS NOT NULL AND
pcr.document_line_id = prla.requisition_line_id AND
prla.requisition_line_id = prda.requisition_line_id ;*/
SELECT -- any quantity change
prla.requisition_line_id,
prda.distribution_id,
prla.requisition_header_id,
prla.unit_price,
nvl(pcr.new_quantity, pcr.old_quantity)
FROM
po_change_requests pcr,
po_req_distributions_all prda,
po_requisition_lines_all prla
WHERE
pcr.change_request_group_id = grp_id AND
pcr.new_quantity IS NOT NULL AND
-- (pcr.new_quantity IS NOT NULL OR pcr.new_need_by_date IS NOT NULL) AND
pcr.action_type = 'MODIFICATION' AND
pcr.document_distribution_id = prda.distribution_id AND
prla.requisition_line_id = prda.requisition_line_id AND
pcr.document_line_id = prla.requisition_line_id ;
SELECT
nvl(fsp.req_encumbrance_flag, 'N')
INTO
l_flag_one
FROM financials_system_parameters fsp;
l_distribution_id_tbl.delete;
-- update new values in PO_ENCUMBRANCE_GT
UPDATE po_encumbrance_gt
SET
amount_ordered = l_new_amount,
quantity_ordered = l_new_quantity,
price = l_new_price,
nonrecoverable_tax = l_new_tax
WHERE
distribution_id = l_distribution_id AND
adjustment_status = po_document_funds_grp.g_adjustment_status_new;
SELECT
prda.distribution_id,
prda.distribution_num,
prda.requisition_line_id,
prla.line_num
INTO
l_fc_req_distr_id,
l_fc_req_distr_num,
l_fc_req_line_id,
l_fc_req_line_num
FROM
po_requisition_lines_all prla,
po_req_distributions_all prda,
po_distributions_all pda
WHERE
pda.po_distribution_id = l_fc_out_tbl.distribution_id(x)
AND pda.req_distribution_id = prda.distribution_id
AND prla.requisition_line_id = prda.requisition_line_id;
* If all requests are valid, update status to "NEW" and kick off workflow
*/
IF(x_errtable.req_line_id.count = 0) THEN
IF g_debug_stmt THEN
po_debug.debug_stmt(l_log_head, x_retmsg, 'all change requests are valid, updating status to "NEW" and kick off workflow');
UPDATE po_change_requests
SET request_status = 'NEW'
WHERE change_request_group_id = p_group_id
AND request_status = 'SYSTEMSAVE';
SELECT nvl(sum(w.shipped_quantity),0)
INTO l_open_quantity
FROM oe_order_lines_all oel
,oe_order_headers_all oeh
,wsh_delivery_details w
,po_requisition_lines_all pol
WHERE
oel.header_id = oeh.header_id
AND oel.line_id = w.source_line_id
AND w.source_code = 'OE'
AND w.released_status = 'C'
AND oel.source_document_line_id=pol.requisition_line_id
AND oel.source_document_id=pol.requisition_header_id
AND oeh.source_document_type_id =10
AND pol.REQUISITION_LINE_ID=p_req_line_id;