[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 requisition_header_id into l_req_document_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_doc_level_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 '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);
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
);
SELECT PLC.displayed_field
INTO l_entity_token
FROM PO_LOOKUP_CODES PLC
WHERE PLC.lookup_type = 'DOCUMENT LEVEL'
AND PLC.lookup_code = p_doc_level
;
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 REPORT.message_type <> 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
)
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
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
)
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
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
;
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