[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE( PLL.value_basis --
, g_order_type_FIXED_PRICE, 'Y'
, g_order_type_RATE, 'Y'
, 'N'
)
INTO l_amt_based_line_flag
FROM PO_LINE_LOCATIONS_ALL PLL
, PO_DISTRIBUTIONS_ALL POD
WHERE POD.po_distribution_id = p_distribution_id
AND PLL.line_location_id = POD.line_location_id
;
SELECT POD.distribution_type
INTO l_distribution_type
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.po_distribution_id = p_distribution_id;
INSERT INTO PO_SESSION_GT TEMP
( key
, num1
, index_num1
)
VALUES
( l_procedure_id
, p_agreement_id_tbl(i)
, PO_SESSION_GT_S.NEXTVAL
)
RETURNING TEMP.index_num1
BULK COLLECT INTO l_sequence_tbl
;
'Bulk Insertion into Session Table success');
UPDATE PO_SESSION_GT TEMP
SET char1 =
(SELECT POH.encumbrance_required_flag
FROM PO_HEADERS POH
WHERE POH.po_header_id = TEMP.num1
)
WHERE TEMP.key = l_procedure_id
;
'Encumbrance information updated successfully');
SELECT rowid BULK COLLECT INTO PO_DEBUG.g_rowid_tbl
FROM PO_SESSION_GT WHERE key = l_procedure_id
;
UPDATE PO_SESSION_GT
SET char1 = NVL(char1,'N')
WHERE key = l_procedure_id
AND index_num1 = l_sequence_tbl(i)
RETURNING char1
BULK COLLECT INTO x_agreement_encumbered_tbl
;
l_gl_packet_status GL_BC_PACKETS.status_code%TYPE; --used in insert_packet
SELECT
GL_SOB.currency_code
, FSP.set_of_books_id
, FSP.req_encumbrance_type_id
, FSP.purch_encumbrance_type_id
INTO
l_currency_code_func
, l_set_of_books_id
, l_req_encumb_type
, l_po_encumb_type
FROM
FINANCIALS_SYSTEM_PARAMETERS FSP
, GL_SETS_OF_BOOKS GL_SOB
WHERE
GL_SOB.set_of_books_id = FSP.set_of_books_id
;
SELECT po_header_id into l_po_document_id FROM po_lines_all
WHERE po_line_id = p_doc_level_id ;
SELECT po_header_id into l_po_document_id FROM po_line_locations_all
WHERE line_location_id = p_doc_level_id ;
SELECT po_header_id into l_po_document_id FROM po_distributions_all
WHERE po_distribution_id = p_doc_level_id ;
SELECT po_header_id into l_rel_document_id FROM po_line_locations_all
WHERE line_location_id = p_doc_level_id ;
SELECT po_header_id into l_rel_document_id FROM po_distributions_all
WHERE po_distribution_id = p_doc_level_id ;
SELECT Nvl(par_flag,'N')
INTO l_is_par_dummy_req
FROM po_requisition_headers_all
WHERE REQUISITION_HEADER_ID = l_req_document_id;
SELECT requisition_header_id into l_req_document_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_doc_level_id ;
SELECT Nvl(par_flag,'N')
INTO l_is_par_dummy_req
FROM po_requisition_headers_all
WHERE REQUISITION_HEADER_ID = l_req_document_id;
SELECT requisition_header_id into l_req_document_id
FROM PO_REQUISITION_LINES_ALL POL, PO_REQ_DISTRIBUTIONS_ALL POD
WHERE POL.REQUISITION_LINE_ID = POD.REQUISITION_LINE_ID
AND POD.DISTRIBUTION_ID =p_doc_level_id;
SELECT Nvl(par_flag,'N')
INTO l_is_par_dummy_req
FROM po_requisition_headers_all
WHERE REQUISITION_HEADER_ID = l_req_document_id;
SELECT 'N'
INTO l_partial_flag
FROM PO_ENCUMBRANCE_GT
WHERE origin_sequence_num IS NOT NULL
AND rownum = 1;
SELECT distribution_type,
distribution_id
BULK COLLECT INTO l_distribution_type_tbl, l_distribution_id_tbl
FROM po_encumbrance_gt
ORDER BY line_location_id, distribution_id;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update po_encumbrance_gt with the JAI non-recoverable tax');
UPDATE po_encumbrance_gt
SET nonrecoverable_tax = nvl(l_nonrecoverable_tax_tbl(indx),0)
WHERE distribution_id = l_distribution_id_tbl(indx)
AND distribution_type = l_distribution_type_tbl(indx);
UPDATE po_distributions_all
SET nonrecoverable_tax = nvl(l_nonrecoverable_tax_tbl(indx),0)
WHERE po_distribution_id = l_distribution_id_tbl(indx);
UPDATE po_req_distributions_all
SET nonrecoverable_tax = nvl(l_nonrecoverable_tax_tbl(indx),0)
WHERE distribution_id = l_distribution_id_tbl(indx);
PO_ENCUMBRANCE_POSTPROCESSING.insert_packet(
p_status_code => l_gl_packet_status
, p_user_id => l_user_id
, p_set_of_books_id => l_set_of_books_id
, p_currency_code => l_currency_code_func
, p_action => l_action --bug#5646605 added the p_action parameter to derive entity/even type codes
, x_packet_id => l_packet_id
);
PO_ENCUMBRANCE_POSTPROCESSING.update_document_encumbrance(
p_doc_type => p_doc_type
, p_doc_subtype => p_doc_subtype
, p_action => p_action
, p_gl_return_code => l_gl_return_code
);
SELECT DISTINCT DISTS.header_id
BULK COLLECT INTO l_doc_id_tbl
FROM PO_ENCUMBRANCE_GT DISTS
;
SELECT DECODE( p_doc_type
, g_doc_type_RELEASE, DISTS.po_release_id
, DISTS.header_id
)
INTO l_document_id
FROM PO_ENCUMBRANCE_GT DISTS
WHERE DISTS.origin_sequence_num IS NULL
AND rownum = 1
;
--Added this IF Condition to Avoid Inserting Action History Record, for Funds Check Failed cases. -- Bug 4661095
PO_ENCUMBRANCE_POSTPROCESSING.create_enc_action_history(
p_doc_type => p_doc_type
, p_doc_id_tbl => l_doc_id_tbl
, p_employee_id => p_employee_id
, p_action => p_action
, p_cbc_flag => l_cbc_flag
);
/*BEGIN : Bug 13077585 :Reverting the fix of delete unprocessesed events
to fix the issue of view results screen. The earlier fix has an impact on
federal customers to review the amounts for the account.
/* 12405805
This deletes unprocessed events arised out of checkfunds action
and invalid events arised out of exceptions
IF( p_check_only_flag = 'Y'
OR
(l_exc_code = g_EXECUTE_GL_CALL_EXC_CODE)
OR
(l_gl_return_code = 'F')
) THEN
IF g_debug_stmt THEN
PO_DEBUG.debug_var(l_log_head,l_progress,'Call made to', 'delete_unnecessary_events');
PO_ENCUMBRANCE_POSTPROCESSING.delete_unnecessary_events(l_old_pkt_id, l_action);
SELECT PLC.displayed_field
INTO l_entity_token
FROM PO_LOOKUP_CODES PLC
WHERE PLC.lookup_type = 'DOCUMENT LEVEL'
AND PLC.lookup_code = NVL(p_doc_level,'HEADER')
;
select ae_event_id INTO PO_ENCUMBRANCE_POSTPROCESSING.g_event_id
from po_bc_distributions
where packet_id = l_old_pkt_id
and rownum = 1;
/*BEGIN : Bug 13077585 :Reverting the fix of delete unprocessesed events
to fix the issue of view results screen. The earlier fix has an impact on
federal customers to review the amounts for the account.
/* 12405805
This deletes unprocessed events arised out of checkfunds action
and invalid events arised out of exceptions
IF( (p_check_only_flag = 'Y') OR (l_exc_code = g_EXECUTE_GL_CALL_EXC_CODE) OR(l_gl_return_code = 'F')) THEN
PO_ENCUMBRANCE_POSTPROCESSING.delete_unnecessary_events(l_old_pkt_id, l_action);
SELECT
REPORT.sequence
, REPORT.transaction_id
, REPORT.line_num
, REPORT.shipment_num
, REPORT.distribution_num
, REPORT.transaction_location
, REPORT.message_type
, REPORT.text_line
BULK COLLECT INTO
x_report_object.row_index
, x_report_object.distribution_id
, x_report_object.line_num
, x_report_object.shipment_num
, x_report_object.distribution_num
, x_report_object.result_code
, x_report_object.msg_type
, x_report_object.error_msg
FROM PO_ONLINE_REPORT_TEXT REPORT
WHERE REPORT.online_report_id = p_online_report_id
AND ((l_report_successes = g_parameter_NO
AND Nvl(REPORT.message_type,g_result_ERROR) <> g_result_SUCCESS)
OR l_report_successes = g_parameter_YES
)
;
INSERT INTO PO_SESSION_GT TEMP ( key, num1 )
VALUES ( l_dist_id_key, l_dist_id_tbl(i) )
;
INSERT INTO PO_ENCUMBRANCE_GT
( adjustment_status
, distribution_type
, header_id
, line_id
, line_location_id
, distribution_id
, segment1
, line_num
, distribution_num
, reference_num
, item_description
, budget_account_id
, gl_encumbered_date
, value_basis --
, payment_type --
, encumbered_amount
, amount_ordered
, quantity_ordered
, quantity_delivered
, quantity_on_line
, unit_meas_lookup_code
, item_id
, price
, nonrecoverable_tax
, prevent_encumbrance_flag
, modified_by_agent_flag --bug 3537764
, transferred_to_oe_flag
, source_type_code
, encumbered_flag
, cancel_flag
, closed_code
, project_id
, task_id
, award_num
, expenditure_type
, expenditure_organization_id
, expenditure_item_date
, vendor_id
/* <> */
, CLM_DOC_FLAG
, FUNDED_VALUE
, QUANTITY_FUNDED
, AMOUNT_FUNDED
, CHANGE_IN_FUNDED_VALUE
/* <> */
)
SELECT
p_adjustment_status_tbl(i)
, g_dist_type_REQUISITION
, PRH.requisition_header_id
, PRL.requisition_line_id
, PRL.line_location_id
, PRD.distribution_id
, PRH.segment1
, PRL.line_num
, PRD.distribution_num
, PRL.reference_num
, PRL.item_description
, PRD.budget_account_id
, PRD.gl_encumbered_date
, PRL.order_type_lookup_code --
, NULL --
, PRD.encumbered_amount
, PRD.req_line_amount
, PRD.req_line_quantity
, PRL.quantity_delivered
, PRL.quantity
, PRL.unit_meas_lookup_code
, PRL.item_id
, PRL.unit_price
, PRD.nonrecoverable_tax
, PRD.prevent_encumbrance_flag
, PRL.modified_by_agent_flag --bug 3537764
, PRH.transferred_to_oe_flag
, PRL.source_type_code
, PRD.encumbered_flag
, PRL.cancel_flag
, PRL.closed_code
, PRD.project_id
, PRD.task_id
, PRD.award_id-- Bug #4675692
, PRD.expenditure_type
, PRD.expenditure_organization_id
, PRD.expenditure_item_date
, PRL.vendor_id
/* <> */
, PRH.FEDERAL_FLAG
, PRD.FUNDED_VALUE
, PRD.QUANTITY_FUNDED
, PRD.AMOUNT_FUNDED
, PRD.CHANGE_IN_FUNDED_VALUE
/* <> */
FROM
PO_REQ_DISTRIBUTIONS_ALL PRD
, PO_REQUISITION_LINES_ALL PRL
, PO_REQUISITION_HEADERS_ALL PRH
, PO_SESSION_GT DIST_IDS
WHERE PRH.requisition_header_id = PRL.requisition_header_id --JOIN
AND PRL.requisition_line_id = PRD.requisition_line_id --JOIN
AND PRD.distribution_id = DIST_IDS.num1 --JOIN
AND DIST_IDS.key = l_dist_id_key
;
INSERT INTO PO_ENCUMBRANCE_GT
( adjustment_status
, distribution_type
, header_id
, po_release_id
, line_id
, line_location_id
, distribution_id
, from_header_id
, source_distribution_id
, req_distribution_id
, segment1
, line_num
, shipment_num
, distribution_num
, item_description
, comments
, budget_account_id
, gl_encumbered_date
, value_basis --
, payment_type --
, accrue_on_receipt_flag
, amount_to_encumber
, unencumbered_amount
, encumbered_amount
, amount_ordered
, amount_delivered
, amount_billed
, amount_cancelled
, unencumbered_quantity
, quantity_ordered
, quantity_delivered
, quantity_billed
, quantity_cancelled
, unit_meas_lookup_code
, item_id
, price
, nonrecoverable_tax
, currency_code
, rate
, prevent_encumbrance_flag
, encumbrance_required_flag
, encumbered_flag
, cancel_flag
, closed_code
, approved_flag
, project_id
, task_id
, award_num
, expenditure_type
, expenditure_organization_id
, expenditure_item_date
, vendor_id
/* <> */
, CLM_DOC_FLAG
, FUNDED_VALUE
, QUANTITY_FUNDED
, AMOUNT_FUNDED
, CHANGE_IN_FUNDED_VALUE
/* <> */
, amount_changed_flag
--
-- populating amount_changed_flag from po_distributions_all table.
)
SELECT
p_adjustment_status_tbl(i)
, POD.distribution_type
, POD.po_header_id
, POD.po_release_id
, POD.po_line_id
, POD.line_location_id
, POD.po_distribution_id
, POL.from_header_id
, POD.source_distribution_id
, POD.req_distribution_id
, POH.segment1
, POL.line_num
, POLL.shipment_num
, POD.distribution_num
, POL.item_description
, POH.comments
, POD.budget_account_id
, POD.gl_encumbered_date
, POLL.value_basis --
, POLL.payment_type --
, POLL.accrue_on_receipt_flag
, POD.amount_to_encumber
, POD.unencumbered_amount
, POD.encumbered_amount
, POD.amount_ordered
, POD.amount_delivered
, POD.amount_billed
, POD.amount_cancelled
, POD.unencumbered_quantity
, POD.quantity_ordered
, POD.quantity_delivered
, POD.quantity_billed
, POD.quantity_cancelled
, POLL.unit_meas_lookup_code --: use line loc value
, POL.item_id
, POLL.price_override
, POD.nonrecoverable_tax
, POH.currency_code
, POD.rate
, POD.prevent_encumbrance_flag
, POH.encumbrance_required_flag
, POD.encumbered_flag
, DECODE( POD.distribution_type
, g_dist_type_AGREEMENT, POH.cancel_flag
, POLL.cancel_flag
)
, DECODE( POD.distribution_type
, g_dist_type_AGREEMENT, POH.closed_code
, POLL.closed_code
)
, POLL.approved_flag
, POD.project_id
, POD.task_id
, POD.award_id -- Bug #4675692
, POD.expenditure_type
, POD.expenditure_organization_id
, POD.expenditure_item_date
, POH.vendor_id
/* <> */
, 'N' -- Default for clm doc flag
, POD.FUNDED_VALUE
, POD.QUANTITY_FUNDED
, POD.AMOUNT_FUNDED
, POD.CHANGE_IN_FUNDED_VALUE
/* <> */
, POD.amount_changed_flag -- <13503748>
FROM
PO_DISTRIBUTIONS_ALL POD
, PO_LINE_LOCATIONS_ALL POLL
, PO_LINES_ALL POL
, PO_HEADERS_ALL POH
, PO_SESSION_GT DIST_IDS
WHERE POH.po_header_id = POD.po_header_id --JOIN
AND POL.po_line_id(+) = POD.po_line_id --JOIN
-- the distributions of PAs don't have associated lines
AND POLL.line_location_id(+) = POD.line_location_id --JOIN
-- the distributions of PAs don't have associated shipments
AND POD.po_distribution_id = DIST_IDS.num1 --JOIN
AND DIST_IDS.key = l_dist_id_key
;
UPDATE po_encumbrance_gt DISTS
SET DISTS.CLM_DOC_FLAG = PO_PARTIAL_FUNDING_PKG.is_clm_document(g_doc_type_PO, DISTS.HEADER_ID)
WHERE DISTS.distribution_type = 'STANDARD';
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Updated the CLM Doc Flag for Standard PO Distributions.');
PO_ENCUMBRANCE_PREPROCESSING.delete_encumbrance_gt();
INSERT INTO PO_ENCUMBRANCE_GT
( distribution_type
, doc_level
, doc_level_id
, prevent_encumbrance_flag
)
VALUES
( l_distribution_type
, p_doc_level
, p_doc_level_id_tbl(i)
, 'Y'
)
;
DELETE FROM PSA_BC_REPORT_EVENTS_GT;
INSERT INTO PSA_BC_REPORT_EVENTS_GT
(
SOURCE_DISTRIBUTION_ID_NUM_1,
EVENT_ID
)
SELECT PBD.distribution_id,
PBD.ae_event_id
FROM PO_BC_DISTRIBUTIONS PBD
WHERE PBD.ONLINE_REPORT_ID= p_online_report_id;
select 'Y'
into l_events_populated
from dual
where exists(
select 1
from po_online_report_text
where online_report_id=PO_DOCUMENT_FUNDS_PVT.g_ONLINE_REPORT_ID
and show_in_psa_flag='Y'
);
SELECT count(*)
INTO l_bc_xml_count
FROM PSA_BC_XML_CLOB
WHERE application_id = l_application_id
AND sequence_id = p_sequence_id;
select nvl(pod.encumbered_flag, 'N')
into l_found
from po_req_distributions_all prd,
po_requisition_lines_all prl,
po_distributions_all pod
where prd.distribution_id = p_req_dist_id
and prl.requisition_line_id = prd.requisition_line_id
and prl.document_type_code = 'BLANKET'
and pod.po_header_id = prl.blanket_po_header_id
and pod.distribution_type=g_dist_type_AGREEMENT;--bug#5468417
SELECT po_distribution_id
FROM po_distributions_draft_all
WHERE draft_id = p_draft_id;
INSERT INTO po_session_gt TEMP
(KEY,
num1)
VALUES (l_dist_id_key,
L_dist_id_tbl(i));
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Inserting Data into PO Encumbrance Gt for Draft');
INSERT INTO po_encumbrance_gt
(adjustment_status,
distribution_type,
header_id,
po_release_id,
line_id,
line_location_id,
distribution_id,
from_header_id,
source_distribution_id,
req_distribution_id,
segment1,
line_num,
shipment_num,
distribution_num,
item_description,
comments,
budget_account_id,
gl_encumbered_date,
value_basis --
,
payment_type --
,
accrue_on_receipt_flag,
amount_to_encumber,
unencumbered_amount,
encumbered_amount,
amount_ordered,
amount_delivered,
amount_billed,
amount_cancelled,
unencumbered_quantity,
quantity_ordered,
quantity_delivered,
quantity_billed,
quantity_cancelled,
unit_meas_lookup_code,
item_id,
price,
nonrecoverable_tax,
currency_code,
rate,
prevent_encumbrance_flag,
encumbrance_required_flag,
encumbered_flag,
cancel_flag,
closed_code,
approved_flag,
project_id,
task_id,
award_num,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
vendor_id,
clm_doc_flag,
funded_value,
quantity_funded,
amount_funded,
change_in_funded_value)
SELECT NULL,
pod.distribution_type,
pod.po_header_id,
pod.po_release_id,
pod.po_line_id,
pod.line_location_id,
pod.po_distribution_id,
pol.from_header_id,
pod.source_distribution_id,
pod.req_distribution_id,
poh.segment1,
pol.line_num,
poll.shipment_num,
pod.distribution_num,
pol.item_description,
poh.comments,
pod.budget_account_id,
pod.gl_encumbered_date,
poll.value_basis,
poll.payment_type,
poll.accrue_on_receipt_flag,
pod.amount_to_encumber,
pod.unencumbered_amount,
pod.encumbered_amount,
pod.amount_ordered,
pod.amount_delivered,
pod.amount_billed,
pod.amount_cancelled,
pod.unencumbered_quantity,
pod.quantity_ordered,
pod.quantity_delivered,
pod.quantity_billed,
pod.quantity_cancelled,
poll.unit_meas_lookup_code, --: use line loc value
pol.item_id,
poll.price_override,
pod.nonrecoverable_tax,
poh.currency_code,
pod.rate,
pod.prevent_encumbrance_flag,
poh.encumbrance_required_flag,
pod.encumbered_flag,
Decode(pod.distribution_type,g_dist_type_agreement,poh.cancel_flag,
poll.cancel_flag),
Decode(pod.distribution_type,g_dist_type_agreement,poh.closed_code,
poll.closed_code),
poll.approved_flag,
pod.project_id,
pod.task_id,
pod.award_id,
pod.expenditure_type,
pod.expenditure_organization_id,
pod.expenditure_item_date,
poh.vendor_id,
'Y',
pod.funded_value,
pod.quantity_funded,
pod.amount_funded,
pod.change_in_funded_value
FROM po_distributions_merge_v pod,
po_line_locations_merge_v poll,
po_lines_merge_v pol,
po_headers_merge_v poh,
po_session_gt dist_ids
WHERE poh.po_header_id = pod.po_header_id --JOIN
AND pol.po_line_id (+) = pod.po_line_id --JOIN
-- the distributions of PAs don't have associated lines
AND poll.line_location_id (+) = pod.line_location_id --JOIN
-- the distributions of PAs don't have associated shipments
AND pod.po_distribution_id = dist_ids.num1 --JOIN
AND dist_ids.KEY = l_dist_id_key
AND pod.draft_id = p_draft_id
AND pol.draft_id = p_draft_id
AND poh.draft_id = p_draft_id
AND poll.draft_id = p_draft_id;