[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT po_requisition_headers_s.NEXTVAL
INTO l_req_header_id
FROM dual;
SELECT vendor_id,
vendor_site_id,
vendor_contact_id
INTO l_vendor_id, l_vendor_site_id, l_vendor_contact_id
FROM po_headers_draft_all
WHERE po_header_id = p_po_header_id
AND draft_id = p_par_draft_id;
INSERT INTO po_requisition_headers_all
(requisition_header_id,
preparer_id,
segment1,
summary_flag,
enabled_flag,
description,
--authorization_status,
type_lookup_code,
org_id,
federal_flag,
par_flag,
par_draft_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
SELECT l_req_header_id,
POD.agent_id PREPARER_ID,
POD.draft_id SEGMENT1,
'N' SUMMARY_FLAG,
'Y' ENABLED_FLAG,
POD.justification DESCRIPTION,
--POH.authorization_status,
'PURCHASE' TYPE_LOOKUP_CODE,
POH.org_id,
'Y' FEDERAL_FLAG,
'Y' PAR_FLAG,
p_par_draft_id PAR_DRAFT_ID,
SYSDATE LAST_UPDATE_DATE,
POH.last_updated_by,
POH.last_update_login,
SYSDATE CREATION_DATE,
POH.created_by
FROM po_headers_draft_all POH,
po_drafts POD
WHERE POH.draft_id = p_par_draft_id
AND POH.po_header_id = p_po_header_id
AND POD.document_id = POH.po_header_id
AND POD.draft_id = POH.draft_id;
||l_api_name, l_progress, 'INSERTED: '
|| l_count
|| ' ROWS ');
SELECT po_line_id
bulk collect INTO l_po_line_id_tbl
FROM po_lines_draft_all
WHERE po_header_id = p_po_header_id
AND draft_id = p_par_draft_id;
SELECT po_requisition_lines_s.NEXTVAL
INTO l_req_line_id
FROM dual;
INSERT INTO po_requisition_lines_all
(requisition_line_id,
requisition_header_id,
line_num,
line_type_id,
category_id,
item_description,
unit_meas_lookup_code,
unit_price,
quantity,
to_person_id,
source_type_code,
item_id,
item_revision,
need_by_date,
justification,
currency_code,
order_type_lookup_code,
purchase_basis,
matching_basis,
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator,
clm_base_line_num,
clm_option_num,
clm_option_from_date,
clm_option_to_date,
clm_option_exercised,
clm_exercised_date,
org_id,
par_draft_id,
par_line_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
--reqs_in_pool_flag,
amount,
destination_type_code,
destination_organization_id,
suggested_buyer_id,
vendor_id,
vendor_site_id,
vendor_contact_id)
SELECT l_req_line_id,
l_req_header_id,
PLD.line_num,
PLT.line_type_id,
PLD.category_id,
PLD.item_description,
PLD.unit_meas_lookup_code,
PLD.unit_price,
PLD.quantity,
POD.agent_id TO_PERSON_ID,
'VENDOR' SOURCE_TYPE_CODE,
PLD.item_id,
PLD.item_revision,
(SELECT need_by_date
FROM po_line_locations_draft_all PLLD
WHERE PLD.po_header_id = PLLD.po_header_id
AND PLD.po_line_id = PLLD.po_line_id
AND PLD.draft_id = PLLD.draft_id
AND ROWNUM < 2) NEED_BY_DATE,
PLD.comments,
POH.currency_code,
PLT.order_type_lookup_code,
PLT.purchase_basis,
PLT.matching_basis,
PLD.line_num_display,
PLD.group_line_id,
PLD.clm_info_flag,
PLD.clm_option_indicator,
PLD.clm_base_line_num,
PLD.clm_option_num,
PLD.clm_option_from_date,
PLD.clm_option_to_date,
PLD.clm_exercised_flag,
PLD.clm_exercised_date,
PLD.org_id,
PLD.draft_id PAR_DRAFT_ID,
PLD.po_line_id PAR_LINE_ID,
SYSDATE LAST_UPDATE_DATE,
PLD.last_updated_by,
PLD.last_update_login,
SYSDATE CREATION_DATE,
PLD.created_by,
--'Y' REQS_IN_POOL_FLAG,
PLD.amount AMOUNT,
pldor.destination_type_code,
pldor.destination_organization_id,
POD.agent_id,
l_vendor_id,
l_vendor_site_id,
l_vendor_contact_id
FROM po_lines_draft_all PLD,
po_headers_draft_all POH,
po_drafts POD,
po_line_types_b plt,
(SELECT po_line_id,
draft_id,
po_header_id,
destination_type_code,
destination_organization_id
FROM po_distributions_draft_all
WHERE po_line_id = L_po_line_id_tbl(i)
AND draft_id = p_par_draft_id
AND po_header_id = p_po_header_id
AND ROWNUM = 1) pldor
WHERE PLD.po_header_id = p_po_header_id
AND PLT.line_type_id = NVL(pld.line_type_id,1)
AND PLD.draft_id = p_par_draft_id
AND PLD.po_line_id = L_po_line_id_tbl(i)
AND POH.po_header_id = PLD.po_header_id
AND POH.draft_id = PLD.draft_id
AND POD.document_id = POH.po_header_id
AND POD.draft_id = POH.draft_id
AND pld.draft_id = pldor.draft_id(+)
AND pld.po_header_id = pldor.po_header_id(+)
AND pld.po_line_id = pldor.po_line_id(+);
INSERT INTO po_req_distributions_all
(distribution_id,
requisition_line_id,
set_of_books_id,
code_combination_id,
req_line_quantity,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name,
encumbered_amount,
budget_account_id,
accrual_account_id,
variance_account_id,
prevent_encumbrance_flag,
gl_closed_date,
distribution_num,
org_id,
partial_funded_flag,
clm_misc_loa,
change_in_funded_value,
unencumbered_amount,
funds_liquidated,
par_draft_id,
par_distribution_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
req_line_amount,
quantity_funded,
amount_funded,
funded_value)
SELECT po_req_distributions_s.NEXTVAL,
l_req_line_id,
set_of_books_id,
code_combination_id,
quantity_ordered REQ_LINE_QUANTITY,
'N' encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name,
encumbered_amount,
budget_account_id,
accrual_account_id,
variance_account_id,
prevent_encumbrance_flag,
gl_closed_date,
distribution_num,
org_id,
partial_funded_flag,
clm_misc_loa,
change_in_funded_value,
unencumbered_amount,
NULL FUNDS_LIQUIDATED,
draft_id PAR_DRAFT_ID,
po_distribution_id PAR_DISTRIBUTION_ID,
SYSDATE LAST_UPDATE_DATE,
last_updated_by,
last_update_login,
SYSDATE CREATION_DATE,
created_by,
amount_ordered REQ_LINE_AMOUNT,
( CASE
WHEN old_quantity_ordered IS NULL
AND quantity_ordered IS NULL THEN NULL
ELSE Nvl(old_quantity_ordered, 0) -
Nvl(quantity_ordered, 0)
END ) QUANTITY_FUNDED,
( CASE
WHEN amount_ordered IS NULL
AND old_amount_ordered IS NULL THEN NULL
ELSE Nvl(old_amount_ordered, 0) - Nvl(amount_ordered, 0)
END ) AMOUNT_FUNDED,
funded_value
FROM po_distributions_draft_all PDD
WHERE PDD.po_header_id = p_po_header_id
AND change_status = 'NEW'
AND PDD.draft_id = p_par_draft_id
AND PDD.po_line_id = L_po_line_id_tbl(i);
INSERT INTO po_requisition_lines_all
(requisition_line_id,
requisition_header_id,
line_num,
line_type_id,
category_id,
item_description,
unit_meas_lookup_code,
unit_price,
quantity,
to_person_id,
source_type_code,
item_id,
item_revision,
--need_by_date,
justification,
currency_code,
order_type_lookup_code,
purchase_basis,
matching_basis,
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator,
clm_base_line_num,
clm_option_num,
clm_option_from_date,
clm_option_to_date,
clm_option_exercised,
clm_exercised_date,
org_id,
par_draft_id,
par_line_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
--reqs_in_pool_flag,
amount,
suggested_buyer_id,
vendor_id,
vendor_site_id,
vendor_contact_id)
SELECT po_requisition_lines_s.NEXTVAL,
l_req_header_id,
(SELECT Nvl(Max(line_num), 0) + 1
FROM po_requisition_lines_all
WHERE requisition_header_id = l_req_header_id) LINE_NUM,
PLT.line_type_id,
NULL CATEGORY_ID,
pod.justification ITEM_DESCRIPTION,
NULL
UNIT_MEAS_LOOKUP_CODE,
NULL UNIT_PRICE,
NULL QUANTITY,
PHD.agent_id TO_PERSON_ID,
'VENDOR' SOURCE_TYPE_CODE,
NULL ITEM_ID,
NULL ITEM_REVISION,
--SYSDATE NEED_BY_DATE,
pod.justification JUSTIFICATION,
PHD.currency_code,
PLT.order_type_lookup_code,
PLT.purchase_basis,
PLT.matching_basis,
'0000' LINE_NUM_DISPLAY,
NULL GROUP_LINE_ID,
'Y' CLM_INFO_FLAG,
NULL
CLM_OPTION_INDICATOR
,
NULL
CLM_BASE_LINE_NUM,
NULL CLM_OPTION_NUM,
NULL
CLM_OPTION_FROM_DATE
,
NULL
CLM_OPTION_TO_DATE,
NULL
CLM_OPTION_EXERCISED
,
NULL
CLM_EXERCISED_DATE,
PHD.org_id,
PHD.draft_id PAR_DRAFT_ID,
NULL PAR_LINE_ID,
PHD.last_update_date,
PHD.last_updated_by,
PHD.last_update_login,
SYSDATE CREATION_DATE,
PHD.created_by,
--'Y' REQS_IN_POOL_FLAG,
NULL AMOUNT,
PHD.agent_id,
l_vendor_id,
l_vendor_site_id,
l_vendor_contact_id
FROM po_headers_draft_all PHD,
po_drafts pod,
po_line_types_b PLT
WHERE PLT.line_type_id = 1
AND PHD.draft_id = p_par_draft_id
AND PHD.po_header_id = p_po_header_id
AND pod.document_id = phd.po_header_id
AND pod.draft_id = phd.draft_id
AND PHD.change_status = 'UPDATE';
UPDATE po_requisition_lines_all prla1
SET clm_base_line_num = nvl((SELECT requisition_line_id
FROM po_requisition_lines_all prla2
WHERE prla2.par_line_id = prla1.clm_base_line_num
AND prla2.requisition_header_id
= prla1.requisition_header_id),0) --bug 16483241
WHERE prla1.requisition_header_id = l_req_header_id
AND clm_base_line_num IS NOT NULL;
UPDATE po_requisition_lines_all prla1
SET group_line_id = nvl((SELECT requisition_line_id
FROM po_requisition_lines_all prla2
WHERE prla2.par_line_id = prla1.group_line_id
AND prla2.requisition_header_id
= prla1.requisition_header_id),0)--bug 16483241
WHERE prla1.requisition_header_id = l_req_header_id
AND group_line_id IS NOT NULL;
||l_api_name, l_progress, 'Number of SLINS Updated: '
|| l_count);
po_wf_debug_pkg.Insert_debug(itemtype, itemkey, l_progress);
po_wf_debug_pkg.Insert_debug(itemtype, itemkey, l_progress);
po_wf_debug_pkg.Insert_debug(itemtype, itemkey, l_progress);
Select preparer_id
INTO p_preparer_id
FROM po_requisition_headers_all
WHERE
requisition_header_id = p_par_req_header_id;
PROCEDURE Par_req_status_update_wf(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT nocopy VARCHAR2)
IS
l_par_req_header_id NUMBER;
Par_req_status_update(l_par_req_header_id);
END par_req_status_update_wf;
PROCEDURE Par_req_status_update(l_par_req_header_id NUMBER)
IS
PRAGMA autonomous_transaction;
UPDATE po_requisition_headers_all
SET authorization_status = 'APPROVED'
WHERE requisition_header_id = l_par_req_header_id;
UPDATE po_requisition_lines_all
SET reqs_in_pool_flag = 'Y'
WHERE requisition_header_id = l_par_req_header_id;
END par_req_status_update;