[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_encumbrance_gt(
p_action IN VARCHAR2
, p_distribution_type IN VARCHAR2
, p_main_or_backing IN VARCHAR2
, p_origin_seq_num_tbl IN po_tbl_number
, p_backing_dist_id_tbl IN po_tbl_number
, p_ap_budget_account_id IN NUMBER
, x_count OUT NOCOPY NUMBER
);
PROCEDURE update_amounts(
p_action IN VARCHAR2,
p_currency_code_func IN VARCHAR2
);
delete_encumbrance_gt();
UPDATE PO_ENCUMBRANCE_GT
SET
sequence_num = NULL
, origin_sequence_num = NULL
;
SELECT COUNT(*)
INTO x_count
FROM PO_ENCUMBRANCE_GT ENC
WHERE ENC.sequence_num IS NULL
;
update_encumbrance_gt(
p_action => p_action
, p_distribution_type => p_distribution_type
, p_main_or_backing => p_main_or_backing
, p_origin_seq_num_tbl => l_origin_seq_num_tbl
, p_backing_dist_id_tbl => l_doc_level_id_tbl
, p_ap_budget_account_id => p_ap_budget_account_id
, x_count => x_count
);
DELETE FROM PO_ENCUMBRANCE_GT ENC
WHERE
-- Delete already encumbered rows for Reserve, FC
-- Bug 3364450: also delete enc rows for INV Cancel
/* <> */
--Do Not Delete the distributions with Encumbered Flag as Y for clm documents in case of Reserve
/* <> */
-- <13503748: Edit without unreserve ER START>
-- for reserve action delete only those records which have encumbered
-- flag as 'Y' and amount_changed_flag as 'N'
-- For bug 16701352 adding nvl condition on CLM_DOC_FLAG for commercial dists check
( p_action = g_action_RESERVE AND NVL(ENC.encumbered_flag,'N') = 'Y' AND
( (Nvl(CLM_DOC_FLAG,'N') = 'N'
AND Nvl(ENC.amount_changed_flag,'N') = 'N') OR -- Commerical Dists
(CLM_DOC_FLAG = 'Y' AND nvl(change_in_funded_value,0) = 0)) -- CLM Dists with no change in funds
)
-- <13503748 END>
-- Bug16208745 Those distributions
-- modified with 'Change Amount' action should
-- not be considered for UNRESERVE action
OR ( p_action = g_action_UNRESERVE
AND Nvl(ENC.amount_changed_flag,'N') = 'Y')
OR ( p_action IN ( g_action_UNDO_FINAL_CLOSE
, g_action_INVOICE_CANCEL
, g_action_CR_MEMO_CANCEL)
AND NVL(ENC.encumbered_flag,'N') = 'Y' )
-- Delete already unencumbered rows for reversal actions
-- Bug 3402031: Allow all unencumbered rows for FINAL CLOSE action
-- Later, only rows unenc due to cancellation are kept for FINAL CLOSE.
OR ( p_action NOT IN ( g_action_RESERVE
, g_action_UNDO_FINAL_CLOSE
, g_action_ADJUST
, g_action_INVOICE_CANCEL
, g_action_CR_MEMO_CANCEL
, g_action_FINAL_CLOSE)
AND NVL(ENC.encumbered_flag,'N') = 'N' )
-- Delete all cancelled rows unless action is Final Close
-- But 3477327: also keep cancelled rows for INV CANCEL
OR ( p_action NOT IN ( g_action_FINAL_CLOSE
, g_action_INVOICE_CANCEL
, g_action_CR_MEMO_CANCEL)
AND NVL(ENC.cancel_flag,'N') = 'Y'
)
-- Bug 3477327: For Inv/Cr Memo Cancel, now delete cancelled
-- rows unless they are ALSO finally closed rows
OR ( p_action IN (g_action_INVOICE_CANCEL, g_action_CR_MEMO_CANCEL)
AND NVL(ENC.cancel_flag, 'N') = 'Y'
AND NVL(ENC.closed_code, g_clsd_OPEN) <> g_clsd_FINALLY_CLOSED
)
-- Delete all FC'ed rows unless action is INV Cancel, Undo FC
OR ( NVL(ENC.closed_code, g_clsd_OPEN) = g_clsd_FINALLY_CLOSED
AND p_action NOT IN ( g_action_INVOICE_CANCEL
, g_action_CR_MEMO_CANCEL
, g_action_UNDO_FINAL_CLOSE)
)
-- Bug 3402031: Delete unencumbered rows during FINAL CLOSE if
-- a CANCEL action has not been taken.
-- Bug 5115105: only keep Cancelled rows for POs/Releases
-- If the cancelled row belongs to a requisition then delete it.
OR ( p_action = g_action_FINAL_CLOSE
AND (
(ENC.distribution_type=g_dist_type_REQUISITION
AND NVL(ENC.cancel_flag, 'N') = 'Y')
OR
NVL(ENC.cancel_flag, 'N') = 'N'
)
and NVL(ENC.encumbered_flag, 'N') = 'N')
--Exclude certain Req distributions
OR ( ENC.distribution_type = g_dist_type_REQUISITION
AND ( -- ENC.line_location_id IS NOT NULL /* <> */
-- We have to retain the Req Lines with Ship Id Not Null for CLM PR Amendment Reserve Actions
((ENC.line_location_id IS NOT NULL AND Nvl(CLM_DOC_FLAG,'N') = 'N') -- Remove if shipment Id is not null and Non CLM Req
OR (ENC.line_location_id IS NOT NULL AND Nvl(CLM_DOC_FLAG,'N') = 'Y' AND p_action NOT IN (g_action_RESERVE,g_action_UNRESERVE))) -- or if its a CLM Req and action is Not Reserve
-- Line Type Changes: Liquidate iProcurement Requisition
/* <> */
OR ( p_action IN ( g_action_RESERVE, g_action_UNRESERVE
, g_action_REJECT --, g_action_ADJUST Donot delete for Adjust action since will be used for IR ISO ER
)
AND ENC.transferred_to_oe_flag = 'Y'
AND ENC.source_type_code = g_src_INVENTORY
)
OR ( p_action = g_action_RETURN
AND ENC.source_type_code = g_src_INVENTORY
)
--bug 3537764: exclude parent Req dists that have already
--been split in Req Split from any further Enc action
OR ( ENC.prevent_encumbrance_flag = 'Y'
AND ENC.modified_by_agent_flag = 'Y'
)
)
)
--Exclude BPAs that are not encumbered
OR ( ENC.distribution_type = g_dist_type_AGREEMENT
AND NVL(ENC.encumbrance_required_flag,'N') = 'N'
)
--Exclude certain PO/Rel distributions
-- Bug 3391282: We cannot drop distributions that come from
-- shipments with approved_flag = 'Y' for the reserve action,
-- as we were previously doing to avoid some user warnings.
-- That is because PDOI and CBC both expect that they can
-- call reserve with approved_flag already set to 'Y'.
-- A workaround for the warnings, as explained in the bug,
-- is to drop only those distributions that are both approved
-- and have prevent encumbrance flag = 'Y'.
OR ( ENC.distribution_type IN
( g_dist_type_STANDARD, g_dist_type_PLANNED,
g_dist_type_SCHEDULED, g_dist_type_BLANKET )
AND ENC.approved_flag = 'Y'
AND ( p_action = g_action_REJECT
or
(p_action = g_action_RESERVE
and p_cbc_flag = g_parameter_NO
and ENC.prevent_encumbrance_flag = 'Y'
)
)
)
;
PO_DEBUG.debug_var(l_log_head,l_progress,'Number of rows deleted', SQL%ROWCOUNT);
PROCEDURE update_encumbrance_gt(
p_action IN VARCHAR2
, p_distribution_type IN VARCHAR2
, p_main_or_backing IN VARCHAR2
, p_origin_seq_num_tbl IN po_tbl_number
, p_backing_dist_id_tbl IN po_tbl_number
, p_ap_budget_account_id IN NUMBER
, x_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ENCUMBRANCE_GT';
UPDATE PO_ENCUMBRANCE_GT ENC
SET
-- input columns
ENC.unencumbered_amount = NVL(ENC.unencumbered_amount,0)
, ENC.encumbered_amount = NVL(ENC.encumbered_amount,0)
, ENC.amount_delivered = NVL(ENC.amount_delivered,0)
, ENC.amount_billed = NVL(ENC.amount_billed,0)
, ENC.amount_cancelled = NVL(ENC.amount_cancelled,0)
, ENC.unencumbered_quantity = NVL(ENC.unencumbered_quantity,0)
, ENC.quantity_delivered = NVL(ENC.quantity_delivered,0)
, ENC.quantity_billed = NVL(ENC.quantity_billed,0)
, ENC.quantity_cancelled = NVL(ENC.quantity_cancelled,0)
, ENC.nonrecoverable_tax = NVL(ENC.nonrecoverable_tax,0)
, ENC.rate = NVL(ENC.rate,1)
, ENC.prevent_encumbrance_flag = NVL(ENC.prevent_encumbrance_flag,'N')
-- bug 3537764: add modified_by_agent_flag to temp table
, ENC.modified_by_agent_flag = NVL(ENC.modified_by_agent_flag, 'N')
/* <> */
, ENC.FUNDED_VALUE = NVL(ENC.FUNDED_VALUE,0)
, ENC.QUANTITY_FUNDED = NVL(ENC.QUANTITY_FUNDED,0)
, ENC.AMOUNT_FUNDED = NVL(AMOUNT_FUNDED,0)
, ENC.CHANGE_IN_FUNDED_VALUE = NVL(ENC.CHANGE_IN_FUNDED_VALUE,0)
, ENC.CLM_DOC_FLAG = NVL(ENC.CLM_DOC_FLAG,'N')
/* <> */
-- calculation columns
, ENC.amount_based_flag =
DECODE( ENC.value_basis --: use POLL value basis
, g_order_type_FIXED_PRICE, 'Y'
, g_order_type_RATE, 'Y'
, 'N'
)
--bug 356812: no prevent enc lines are sent to GL
, ENC.send_to_gl_flag = DECODE( ENC.prevent_encumbrance_flag
, 'Y', 'N'
, 'Y'
)
WHERE ENC.sequence_num IS NULL
;
UPDATE PO_ENCUMBRANCE_GT ENC
SET
( ENC.min_acct_unit_foreign
, ENC.cur_precision_foreign
)
=
( SELECT
CUR.minimum_accountable_unit
, CUR.precision
FROM
FND_CURRENCIES CUR
WHERE CUR.currency_code = ENC.currency_code
)
WHERE ENC.sequence_num IS NULL
AND ENC.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT ENC
SET ENC.sequence_num = PO_ENCUMBRANCE_GT_S.nextval
WHERE ENC.sequence_num IS NULL
;
UPDATE PO_ENCUMBRANCE_GT PO_DIST
SET PO_DIST.agreement_dist_id =
( SELECT GA_DIST.po_distribution_id
FROM PO_DISTRIBUTIONS GA_DIST
WHERE GA_DIST.po_header_id = PO_DIST.from_header_id
)
WHERE PO_DIST.distribution_type = g_dist_type_STANDARD
AND PO_DIST.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT REL_DIST
SET REL_DIST.agreement_dist_id =
( SELECT BPA_DIST.po_distribution_id
FROM PO_DISTRIBUTIONS_ALL BPA_DIST
WHERE BPA_DIST.po_header_id = REL_DIST.header_id
AND BPA_DIST.distribution_type = g_dist_type_AGREEMENT
-- we don't want release distributions here.
)
WHERE REL_DIST.distribution_type = g_dist_type_BLANKET
AND REL_DIST.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT BACKING
SET BACKING.origin_sequence_num = p_origin_seq_num_tbl(i)
WHERE BACKING.distribution_id = p_backing_dist_id_tbl(i)
AND BACKING.distribution_type = p_distribution_type
AND BACKING.origin_sequence_num IS NULL
AND rownum = 1
;
UPDATE PO_ENCUMBRANCE_GT BACKING
SET BACKING.send_to_gl_flag = 'N'
, BACKING.update_encumbered_amount_flag = 'N'
WHERE BACKING.origin_sequence_num IS NOT NULL --backing doc
AND BACKING.encumbered_flag = 'N'
AND BACKING.distribution_type IN
(g_dist_type_AGREEMENT, g_dist_type_PLANNED)
;
UPDATE PO_ENCUMBRANCE_GT MAINDOC
SET MAINDOC.budget_account_id = p_ap_budget_account_id
WHERE MAINDOC.origin_sequence_num IS NULL;
END update_encumbrance_gt;
SELECT DISTS.req_distribution_id
BULK COLLECT INTO l_distribution_id_tbl
FROM PO_ENCUMBRANCE_GT DISTS
WHERE DISTS.req_distribution_id IS NOT NULL
AND DISTS.origin_sequence_num IS NULL
AND DISTS.prevent_encumbrance_flag = 'N'
;
SELECT DISTS.source_distribution_id
BULK COLLECT INTO l_distribution_id_tbl
FROM PO_ENCUMBRANCE_GT DISTS
WHERE DISTS.source_distribution_id IS NOT NULL
AND DISTS.origin_sequence_num IS NULL
AND DISTS.prevent_encumbrance_flag = 'N'
;
SELECT DISTS.agreement_dist_id
BULK COLLECT INTO l_distribution_id_tbl
FROM PO_ENCUMBRANCE_GT DISTS
WHERE DISTS.agreement_dist_id IS NOT NULL
AND DISTS.origin_sequence_num IS NULL
AND DISTS.prevent_encumbrance_flag = 'N'
;
INSERT INTO PO_SESSION_GT
( key
, num1 -- main dist's sequence_num
, num2 -- backing req's distribution_id
)
SELECT
l_backing_req_key
, ENC.sequence_num
, PRD.distribution_id
FROM
PO_ENCUMBRANCE_GT ENC
, PO_REQUISITION_LINES_ALL PRL
, PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRD.distribution_id = ENC.req_distribution_id --JOIN
AND PRL.requisition_line_id = PRD.requisition_line_id --JOIN
AND NVL(PRD.prevent_encumbrance_flag,'N') = 'N'
AND NVL(PRL.closed_code,g_clsd_OPEN) <> g_clsd_FINALLY_CLOSED
AND NVL(PRL.cancel_flag,'N') = 'N'
--bug 3401937: backing docs for Invoice Cancel
AND ( (p_action IN (g_action_RESERVE, g_action_INVOICE_CANCEL,
g_action_CR_MEMO_CANCEL)
AND PRD.encumbered_flag = 'Y')
-- picks up backing Req for case of PO Unres when Invoice Cancel
OR
(p_action NOT IN (g_action_RESERVE, g_action_INVOICE_CANCEL,
g_action_CR_MEMO_CANCEL)
AND NVL(PRD.encumbered_flag,'N') = 'N' )
-- for Invoice Cancel, if PO is FC'ed, then backing Req is not
-- encumbered, but we don't act on the backing Req for this case
)
AND ENC.origin_sequence_num IS NULL
AND ENC.prevent_encumbrance_flag = 'N'
;
UPDATE PO_SESSION_GT SCRATCH
SET SCRATCH.num3 =
( SELECT PRD.distribution_id
FROM PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRD.source_req_distribution_id = SCRATCH.num2
)
WHERE SCRATCH.key = l_backing_req_key
;
SELECT rowid BULK COLLECT INTO PO_DEBUG.g_rowid_tbl
FROM PO_SESSION_GT WHERE key = l_backing_req_key ;
SELECT
SCRATCH.num1
, NVL(SCRATCH.num3, SCRATCH.num2)
BULK COLLECT INTO
x_origin_seq_num_tbl
, x_dist_id_tbl
FROM PO_SESSION_GT SCRATCH
WHERE SCRATCH.key = l_backing_req_key
;
SELECT
POD.po_distribution_id
, SR_DIST.sequence_num
BULK COLLECT INTO
x_dist_id_tbl
, x_origin_seq_num_tbl
FROM
PO_DISTRIBUTIONS_ALL POD
, PO_LINE_LOCATIONS_ALL POLL
, PO_ENCUMBRANCE_GT SR_DIST
WHERE POLL.line_location_id = POD.line_location_id --JOIN
AND POD.po_distribution_id = SR_DIST.source_distribution_id --JOIN
AND NVL(POD.prevent_encumbrance_flag,'N') = 'N'
AND NVL(POLL.closed_code,g_clsd_OPEN) <> g_clsd_FINALLY_CLOSED
AND NVL(POLL.cancel_flag,'N') = 'N'
--bug 3401937: backing docs for Invoice Cancel
AND ( (p_action IN (g_action_INVOICE_CANCEL, g_action_CR_MEMO_CANCEL)
AND NVL(SR_DIST.closed_code, g_clsd_OPEN) <> g_clsd_FINALLY_CLOSED
AND POD.encumbered_flag = 'Y'
--bug 3568512: filter on enc_flag = 'Y' only for Invoice Cancel case
--for other unreserved backing PPO actions, maitain unencumbered_amount
)
OR (p_action NOT IN (g_action_INVOICE_CANCEL, g_action_CR_MEMO_CANCEL))
)
AND SR_DIST.origin_sequence_num IS NULL
AND SR_DIST.prevent_encumbrance_flag = 'N'
;
SELECT
POD.po_distribution_id
, REL_DIST.sequence_num
BULK COLLECT INTO
x_dist_id_tbl
, x_origin_seq_num_tbl
FROM
PO_DISTRIBUTIONS_ALL POD
, PO_HEADERS_ALL POH
, PO_ENCUMBRANCE_GT REL_DIST
WHERE POH.po_header_id = POD.po_header_id --JOIN
AND POD.po_distribution_id = REL_DIST.agreement_dist_id --JOIN
AND NVL(POD.prevent_encumbrance_flag,'N') = 'N'
AND NVL(POH.closed_code,g_clsd_OPEN) <> g_clsd_FINALLY_CLOSED
AND NVL(POH.cancel_flag,'N') = 'N'
AND POH.encumbrance_required_flag = 'Y'
--bug 3401937: backing docs for Invoice Cancel
AND ( (p_action IN (g_action_INVOICE_CANCEL, g_action_CR_MEMO_CANCEL)
AND NVL(REL_DIST.closed_code, g_clsd_OPEN) <> g_clsd_FINALLY_CLOSED
AND POD.encumbered_flag = 'Y'
--bug 3568512: filter on enc_flag = 'Y' only for Invoice Cancel case
--for other unreserved backing PPO actions, maitain unencumbered_amount
)
OR (p_action NOT IN (g_action_INVOICE_CANCEL, g_action_CR_MEMO_CANCEL))
)
AND REL_DIST.origin_sequence_num IS NULL
AND REL_DIST.prevent_encumbrance_flag = 'N'
;
SELECT
ENC.doc_level
, ENC.distribution_type
, COUNT(*)
BULK COLLECT INTO
l_doc_level_tbl
, l_dist_type_tbl
, l_count_tbl
FROM PO_ENCUMBRANCE_GT ENC
GROUP BY ENC.doc_level, ENC.distribution_type
;
SELECT DISTINCT ENC.doc_level_id
BULK COLLECT INTO l_doc_level_id_tbl
FROM PO_ENCUMBRANCE_GT ENC
;
delete_encumbrance_gt();
SELECT POD.po_distribution_id
BULK COLLECT INTO l_id_tbl
FROM
PO_DISTRIBUTIONS_ALL POD
, PO_HEADERS_ALL POH
WHERE POD.po_header_id = l_doc_id
AND POD.distribution_type = g_dist_type_AGREEMENT
AND POH.po_header_id = POD.po_header_id
AND POH.encumbrance_required_flag = 'Y'
;
SELECT DECODE( p_doc_type
, g_doc_type_RELEASE, ENC.po_release_id
, ENC.header_id
)
INTO l_doc_id
FROM PO_ENCUMBRANCE_GT ENC
WHERE rownum = 1
;
SELECT 'Y'
INTO l_multiple_docs_flag
FROM PO_ENCUMBRANCE_GT ENC
WHERE (ENC.po_release_id <> l_doc_id
OR ENC.po_release_id IS NULL
)
AND rownum = 1;
SELECT 'Y'
INTO l_multiple_docs_flag
FROM PO_ENCUMBRANCE_GT ENC
WHERE (ENC.header_id <> l_doc_id
OR ENC.header_id IS NULL
)
AND rownum = 1;
UPDATE po_online_report_text
SET show_in_psa_flag='Y'
WHERE ONLINE_report_id=x_sub_check_report_id;
update_amounts(
p_action => p_action,
p_currency_code_func => p_currency_code_func
);
UPDATE PO_ENCUMBRANCE_GT PO_DISTS
SET PO_DISTS.send_to_gl_flag = 'Y',
PO_DISTS.result_type = g_result_NOT_PROCESSED,
PO_DISTS.result_text = l_not_processed_msg
WHERE PO_DISTS.sequence_num IN (SELECT REQ_DISTS.origin_sequence_num
FROM PO_ENCUMBRANCE_GT REQ_DISTS
WHERE REQ_DISTS.amount_based_flag = 'N'
AND REQ_DISTS.prevent_encumbrance_flag = 'N'
AND REQ_DISTS.origin_sequence_num IS NOT NULL
AND REQ_DISTS.distribution_type = g_dist_type_REQUISITION
AND REQ_DISTS.uom_conversion_rate <> -999
);
'Update backing document GL dates'
);
UPDATE PO_ENCUMBRANCE_GT BACKING
SET (
BACKING.period_name
, BACKING.period_year
, BACKING.period_num
, BACKING.quarter_num
, BACKING.gl_period_date --bug#5098665
)
=
(
SELECT
ORIG.period_name
, ORIG.period_year
, ORIG.period_num
, ORIG.quarter_num
, ORIG.gl_period_date --bug#5098665
FROM
PO_ENCUMBRANCE_GT ORIG
WHERE
ORIG.sequence_num = BACKING.origin_sequence_num
AND ORIG.origin_sequence_num IS NULL
AND ORIG.distribution_type <> g_dist_type_REQUISITION
)
WHERE BACKING.origin_sequence_num IS NOT NULL
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.send_to_gl_flag = 'N' --bug 5413111 gl period failed rows shouldn't be sent to GL
, DISTS.gl_result_code = 'F25' --GL error code for no period found
, DISTS.result_type = g_result_ERROR
, DISTS.result_text = l_period_error_text
WHERE DISTS.period_name IS NULL;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.send_to_gl_flag = 'Y' --bug 3568512: new send_to_gl column
, DISTS.result_text = l_not_processed_msg
, DISTS.result_type = g_result_NOT_PROCESSED
WHERE DISTS.period_name IS NOT NULL;
INSERT INTO PO_SESSION_GT
(
key
, index_num1 -- bug3543542 : Save sequence_num to an indexed column
, date1
)
SELECT
l_procedure_id
, DISTS.sequence_num
-- Decide whether to use the override date based on the
-- Use GL Date parameter and the Action
, TRUNC(
DECODE (p_try_dist_date_flag
-- param prefers the distribution date
, 'Y', DISTS.gl_encumbered_date
-- prefer the override date, except for New Adjust lines
, DECODE (DISTS.adjustment_status
, g_adj_status_NEW, DISTS.gl_encumbered_date
, p_override_date
)
)
)
FROM PO_ENCUMBRANCE_GT DISTS
WHERE
DISTS.origin_sequence_num IS NULL --main doc
AND DISTS.period_name IS NULL
--bug 3568512: use send_to_gl_flag for this filter condition
AND (DISTS.send_to_gl_flag = 'Y'
OR (DISTS.distribution_type = g_dist_type_REQUISITION
AND DISTS.prevent_encumbrance_flag = 'Y')
-- only verify GL date information for prevent-enc distributions if
-- they are Req dists. no need to check dates for PO prevent dists.
)
;
INSERT INTO PO_SESSION_GT ( key, date1 )
VALUES ( l_procedure_id, TRUNC(p_override_date))
;
UPDATE PO_SESSION_GT TEMP
SET (
TEMP.char1, -- period_name
TEMP.num2, -- period_year
TEMP.num3, -- period_num
TEMP.num4
)
=
(
SELECT
GL_PS.PERIOD_NAME,
GL_PS.PERIOD_YEAR,
GL_PS.PERIOD_NUM,
GL_PS.QUARTER_NUM
FROM
GL_PERIOD_STATUSES GL_PS,
GL_PERIOD_STATUSES PO_PS,
GL_SETS_OF_BOOKS GL_SOB
WHERE
-- Join conditions:
GL_SOB.set_of_books_id = p_set_of_books_id
AND GL_PS.set_of_books_id = GL_SOB.set_of_books_id
AND PO_PS.set_of_books_id = GL_SOB.set_of_books_id
AND GL_PS.period_name = PO_PS.period_name
-- GL period conditions:
AND GL_PS.application_id = 101
-- bug 5498063
AND (( l_validate_gl_period IN ('Y','R') -- Bug#13717100
and GL_PS.closing_status IN ('O', 'F'))
OR
(l_validate_gl_period = 'N'))
-- AND GL_PS.closing_status IN ('O', 'F')
AND GL_PS.adjustment_period_flag = 'N'
AND GL_PS.period_year <= GL_SOB.latest_encumbrance_year
-- PO period conditions:
AND PO_PS.application_id = 201
AND PO_PS.closing_status = 'O'
AND PO_PS.adjustment_period_flag = 'N'
-- Period date conditions:
AND (TEMP.date1 BETWEEN GL_PS.start_date AND GL_PS.end_date)
)
WHERE TEMP.key = l_procedure_id ;
UPDATE PO_SESSION_GT TEMP
SET (
TEMP.char1, -- period_name
TEMP.num2, -- period_year
TEMP.num3, -- period_num
TEMP.num4, -- quarter_num
TEMP.DATE1 -- gl date to use--
)
=
(
SELECT
GL_PS2.PERIOD_NAME ,
GL_PS2.PERIOD_YEAR ,
GL_PS2.PERIOD_NUM ,
GL_PS2.QUARTER_NUM,
GL_PS2.START_DATE --
FROM
GL_PERIOD_STATUSES GL_PS2
WHERE
GL_PS2.set_of_books_id = p_set_of_books_id AND
GL_PS2.application_id= 101 AND
/* Bug 3967418 Start */
GL_PS2.adjustment_period_flag = 'N' AND
/* Bug 3967418 End */
GL_PS2.start_date=
(SELECT min(gl_ps.start_date)
FROM
GL_PERIOD_STATUSES GL_PS
, GL_PERIOD_STATUSES PO_PS
, GL_SETS_OF_BOOKS GL_SOB
WHERE
-- Join conditions:
GL_SOB.set_of_books_id = p_set_of_books_id
AND GL_PS.set_of_books_id = GL_SOB.set_of_books_id
AND PO_PS.set_of_books_id = GL_SOB.set_of_books_id
AND GL_PS.period_name = PO_PS.period_name
-- GL period conditions:
AND GL_PS.application_id = GL_PS2.application_id
-- bug 5498063
AND (( l_validate_gl_period = 'Y'
and GL_PS.closing_status IN ('O', 'F'))
OR
(l_validate_gl_period = 'N'))
-- AND GL_PS.closing_status IN ('O', 'F')
AND GL_PS.adjustment_period_flag = 'N'
AND GL_PS.period_year <= GL_SOB.latest_encumbrance_year
-- PO period conditions:
AND PO_PS.application_id = 201
AND PO_PS.closing_status = 'O'
AND PO_PS.adjustment_period_flag = 'N'
-- Period date conditions:
AND (TEMP.date1 < GL_PS.start_date)
)
)
WHERE TEMP.key = l_procedure_id;
UPDATE PO_SESSION_GT TEMP
SET (
TEMP.char1, -- period_name
TEMP.num2, -- period_year
TEMP.num3, -- period_num
TEMP.num4, -- quarter_num
TEMP.DATE1 -- gl date to use --
)
=
(
SELECT
GL_PS2.PERIOD_NAME ,
GL_PS2.PERIOD_YEAR ,
GL_PS2.PERIOD_NUM ,
GL_PS2.QUARTER_NUM,
GL_PS2.START_DATE --
FROM
GL_PERIOD_STATUSES GL_PS2
WHERE
GL_PS2.set_of_books_id = p_set_of_books_id AND
GL_PS2.application_id= 101 AND
/* Bug 3967418 Start */
GL_PS2.adjustment_period_flag = 'N' AND
/* Bug 3967418 End */
GL_PS2.end_date=
(SELECT max(gl_ps.end_date)
FROM
GL_PERIOD_STATUSES GL_PS
, GL_PERIOD_STATUSES PO_PS
, GL_SETS_OF_BOOKS GL_SOB
WHERE
-- Join conditions:
GL_SOB.set_of_books_id = p_set_of_books_id
AND GL_PS.set_of_books_id = GL_SOB.set_of_books_id
AND PO_PS.set_of_books_id = GL_SOB.set_of_books_id
AND GL_PS.period_name = PO_PS.period_name
-- GL period conditions:
AND GL_PS.application_id = GL_PS2.application_id
-- bug 5498063
AND (( l_validate_gl_period = 'Y'
and GL_PS.closing_status IN ('O', 'F'))
OR
(l_validate_gl_period = 'N'))
-- AND GL_PS.closing_status IN ('O', 'F')
AND GL_PS.adjustment_period_flag = 'N'
AND GL_PS.period_year <= GL_SOB.latest_encumbrance_year
-- PO period conditions:
AND PO_PS.application_id = 201
AND PO_PS.closing_status = 'O'
AND PO_PS.adjustment_period_flag = 'N'
-- Period date conditions:
AND (TEMP.date1 > GL_PS.end_date)
)
)
WHERE TEMP.key = l_procedure_id;
UPDATE PO_SESSION_GT TEMP
SET (
TEMP.char1, -- period_name
TEMP.num2, -- period_year
TEMP.num3, -- period_num
TEMP.num4, -- quarter_num
TEMP.date1 -- gl_encumbered_date
)
=
(
SELECT
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
QUARTER_NUM,
latest_open_date
FROM(
SELECT
GL_PS.PERIOD_NAME,
GL_PS.PERIOD_YEAR,
GL_PS.PERIOD_NUM,
GL_PS.QUARTER_NUM,
TRUNC(GL_PS.START_DATE) latest_open_date
FROM
GL_PERIOD_STATUSES GL_PS,
GL_PERIOD_STATUSES PO_PS,
GL_SETS_OF_BOOKS GL_SOB
WHERE GL_SOB.set_of_books_id = p_set_of_books_id
AND GL_PS.application_id = 101
AND PO_PS.application_id = 201
AND GL_PS.set_of_books_id = GL_SOB.set_of_books_id --JOIN
AND PO_PS.set_of_books_id = GL_SOB.set_of_books_id --JOIN
AND GL_PS.period_name = PO_PS.period_name --JOIN
AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
AND GL_PS.period_year <= GL_SOB.latest_encumbrance_year
AND PO_PS.closing_status = 'O' -- open
AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
AND TRUNC(GL_PS.start_date) <= Trunc(SYSDATE)
ORDER BY GL_PS.PERIOD_YEAR DESC,
GL_PS.PERIOD_NUM DESC,
GL_PS.QUARTER_NUM DESC)
WHERE ROWNUM = 1
)
WHERE TEMP.key = l_procedure_id
AND TEMP.char1 IS NULL;
SELECT rowid BULK COLLECT INTO PO_DEBUG.g_rowid_tbl
FROM PO_SESSION_GT WHERE key = l_procedure_id
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET (
DISTS.period_name
, DISTS.period_year
, DISTS.period_num
, DISTS.quarter_num
, DISTS.gl_period_date --
)
=
( SELECT /*+ INDEX (VALID_PERIOD PO_SESSION_GT_N2) */
VALID_PERIOD.char1 period_name
, VALID_PERIOD.num2 period_year
, VALID_PERIOD.num3 period_num
, VALID_PERIOD.num4 quarter_num
, VALID_PERIOD.date1 gl_period_date --
FROM PO_SESSION_GT VALID_PERIOD
WHERE
VALID_PERIOD.key = l_procedure_id
AND VALID_PERIOD.index_num1 = DISTS.sequence_num -- bug3543542
AND VALID_PERIOD.char1 IS NOT NULL
)
WHERE
DISTS.origin_sequence_num IS NULL
AND DISTS.period_name IS NULL
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET (
DISTS.period_name
, DISTS.period_year
, DISTS.period_num
, DISTS.quarter_num
, DISTS.gl_period_date --
)
=
( SELECT
VALID_PERIOD.char1 period_name
, VALID_PERIOD.num2 period_year
, VALID_PERIOD.num3 period_num
, VALID_PERIOD.num4 quarter_num
, VALID_PERIOD.date1 gl_period_date --
FROM PO_SESSION_GT VALID_PERIOD
WHERE
VALID_PERIOD.key = l_procedure_id
AND VALID_PERIOD.char1 IS NOT NULL
AND rownum = 1
)
WHERE
DISTS.origin_sequence_num IS NULL
AND DISTS.period_name IS NULL
;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Updated main encumbrance gt with period information');
SELECT 'Y'
INTO x_missing_date_flag
FROM DUAL
WHERE EXISTS
(SELECT 'period information not populated'
FROM PO_SESSION_GT TEMP
WHERE TEMP.key = l_procedure_id
AND TEMP.char1 is NULL -- no period name populated
)
;
DELETE
FROM PO_SESSION_GT TEMP
WHERE TEMP.key = l_procedure_id
;
SELECT
FND_CUR.minimum_accountable_unit
, FND_CUR.precision
INTO
l_min_acct_unit_func
, l_cur_precision_func
FROM FND_CURRENCIES FND_CUR
WHERE FND_CUR.currency_code = p_currency_code_func
;
SELECT header_id
INTO l_header_id
FROM PO_ENCUMBRANCE_GT
WHERE origin_sequence_num is null --main doc
AND rownum = 1 --just get first record since all should have same ID
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.qty_ordered = nvl(DISTS.quantity_ordered, 0)
WHERE DISTS.prevent_encumbrance_flag = 'N'
AND DISTS.amount_based_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.amt_ordered =
DECODE(DISTS.distribution_type
-- Agreements
, g_dist_type_AGREEMENT, amt_to_encumber_func
-- All other doc types
, DECODE(DISTS.amount_based_flag
-- Quantity based lines:
, 'N', DISTS.qty_ordered * DISTS.price
-- Amount based lines:
, DISTS.amount_ordered
)
)
WHERE DISTS.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.nonrecoverable_tax_rate =
DECODE( NVL(DISTS.amt_ordered,0)
-- Bug 3410522: If amt_ordered is 0, we can ignore tax.
, 0, 0
-- Else, calculate rate multiplier for tax
, (1 + (DISTS.nonrecoverable_tax / DISTS.amt_ordered))
)
WHERE DISTS.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.amt_closed =
DISTS.amt_ordered *
DECODE( DISTS.amount_based_flag
-- Quantity-based:
-- closed amt is pro-rated from the req line's qty delivered
-- (used for internal Reqs tied to Sales Orders)
, 'N', (DISTS.quantity_delivered / DISTS.quantity_on_line)
-- Amount-based:
-- The amt_closed for Services Req lines is always zero,
-- as they can not be tied to Sales Orders
, 0
)
WHERE DISTS.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.amt_closed = DISTS.unencumbered_amount
WHERE DISTS.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.qty_closed = DISTS.unencumbered_quantity
WHERE DISTS.origin_sequence_num IS NULL -- main doc
AND DISTS.prevent_encumbrance_flag = 'N'
AND DISTS.amount_based_flag = 'N'
-- no Services lines on a PPO
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.qty_closed =
DECODE( DISTS.accrue_on_receipt_flag
-- Online Accruals
, 'Y', DISTS.quantity_delivered
-- Period-End Accruals:
, DECODE (DISTS.clm_doc_flag,
'Y',
DECODE( p_action
-- Cancel:
, g_action_CANCEL,
GREATEST( DISTS.quantity_billed
, DISTS.quantity_delivered
)
-- Invoices can be made against
-- Cancelled POs. Even if period-end
-- accrual is used, use the delivered
-- qty as closed if its greater than
-- billed qty, because the delivered
-- qty may be invoiced in the future.
-- Actions other than Cancel:
, DISTS.quantity_billed
)
-- Non CLM doc
-- <13503748: Edit without unreserve ER>
-- for all the actions except Finally close making quantity closed
-- as greatest of quantity delivered and quantity billed
-- for period end accruals
, DECODE( p_action
-- Finally Close:
, g_action_FINAL_CLOSE
, DISTS.quantity_billed
, GREATEST( DISTS.quantity_billed
, DISTS.quantity_delivered
)
)
)
)
WHERE DISTS.origin_sequence_num IS NULL -- main doc
AND DISTS.amount_based_flag = 'N'
AND DISTS.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.amt_closed =
DECODE( DISTS.amount_based_flag
-- quantity-based: use qty_closed calc from above
, 'N', DISTS.qty_closed * DISTS.price
-- amount-based: mimic qty_closed calc on amt field analogs
, DECODE( DISTS.accrue_on_receipt_flag
-- Online Accruals:
, 'Y', DISTS.amount_delivered
-- Period-End Accruals:
, DECODE (DISTS.clm_doc_flag,
'Y',
DECODE( p_action
-- Cancel:
, g_action_CANCEL,
GREATEST( DISTS.amount_billed
, DISTS.amount_delivered
)
-- Invoices can be made against
-- Cancelled POs. Even if period-end
-- accrual is used, use the delivered
-- amt as closed if its greater than
-- billed amt, because the delivered
-- amt may be invoiced in the future.
-- Actions other than Cancel:
, DISTS.amount_billed
)
-- Non CLM doc
-- <13503748: Edit without unreserve ER>
-- for all the actions except Finally Close making
-- quantity closed as greatest of quantity
-- delivered and quantity billed for period end accruals
-- Period-End Accruals:
,
DECODE( p_action
-- Finally Close:
, g_action_FINAL_CLOSE
, DISTS.amount_billed
, GREATEST( DISTS.amount_billed
, DISTS.amount_delivered
)
)
)
)
)
WHERE DISTS.origin_sequence_num IS NULL --main doc
AND DISTS.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.qty_closed =
DECODE( p_action
, g_action_INVOICE_CANCEL,
DISTS.quantity_billed + p_ap_cancelled_qty
-- cr memo cancel
, DISTS.quantity_billed
)
WHERE DISTS.prevent_encumbrance_flag = 'N'
AND DISTS.origin_sequence_num IS NULL
AND DISTS.amount_based_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.amt_closed =
DECODE( DISTS.amount_based_flag
, 'N', DISTS.qty_closed * DISTS.price
, DECODE( p_action
, g_action_INVOICE_CANCEL,
p_ap_reinstated_enc_amt + DISTS.amount_billed
-- cr memo cancel
, DISTS.amount_billed
)
)
WHERE DISTS.prevent_encumbrance_flag = 'N'
AND DISTS.origin_sequence_num IS NULL
;
UPDATE PO_ENCUMBRANCE_GT REQ_DISTS
SET REQ_DISTS.uom_conversion_rate =
(SELECT PO_UOM_S.PO_UOM_CONVERT_P( PO_DISTS.unit_meas_lookup_code
, REQ_DISTS.unit_meas_lookup_code
, REQ_DISTS.item_id
)
FROM PO_ENCUMBRANCE_GT PO_DISTS
WHERE REQ_DISTS.origin_sequence_num = PO_DISTS.sequence_num
AND PO_DISTS.distribution_type <> g_dist_type_REQUISITION
AND REQ_DISTS.unit_meas_lookup_code <>
PO_DISTS.unit_meas_lookup_code
)
WHERE REQ_DISTS.distribution_type = g_dist_type_REQUISITION
AND REQ_DISTS.amount_based_flag = 'N'
AND REQ_DISTS.prevent_encumbrance_flag = 'N'
;
update po_encumbrance_gt req_dists
set uom_conversion_rate = 1
where req_dists.clm_doc_flag = 'Y'
and req_dists.uom_conversion_rate = -999
and req_dists.prevent_encumbrance_flag = 'N'
and req_dists.amount_based_flag = 'N'
and req_dists.distribution_type = g_dist_type_REQUISITION;
SELECT 'Y'
INTO l_uom_conversion_error
FROM PO_ENCUMBRANCE_GT REQ_DISTS
WHERE REQ_DISTS.distribution_type = g_dist_type_REQUISITION
AND REQ_DISTS.amount_based_flag = 'N'
AND REQ_DISTS.prevent_encumbrance_flag = 'N'
AND REQ_DISTS.uom_conversion_rate = -999
-- the uom function returns -999 on error
AND rownum = 1 -- only need there to be one for it to be an error
-- (also, without this causes an error for > 1)
;
UPDATE PO_ENCUMBRANCE_GT PO_DISTS
SET PO_DISTS.send_to_gl_flag = 'N',
PO_DISTS.result_type = g_result_ERROR,
PO_DISTS.result_text = l_uom_conversion_err_text
WHERE PO_DISTS.sequence_num IN (SELECT REQ_DISTS.origin_sequence_num
FROM PO_ENCUMBRANCE_GT REQ_DISTS
WHERE REQ_DISTS.amount_based_flag = 'N'
AND REQ_DISTS.prevent_encumbrance_flag = 'N'
AND REQ_DISTS.origin_sequence_num IS NOT NULL
AND REQ_DISTS.distribution_type = g_dist_type_REQUISITION
AND REQ_DISTS.uom_conversion_rate = -999
);
UPDATE PO_ENCUMBRANCE_GT REQ_DISTS
SET REQ_DISTS.amt_closed =
(SELECT
DECODE( REQ_DISTS.amount_based_flag
-- quantity based
, 'N', PO_DISTS.qty_closed
* NVL(REQ_DISTS.uom_conversion_rate,1)
* REQ_DISTS.price
-- amount based
, PO_DISTS.amt_closed_func --bug 3435571
)
FROM PO_ENCUMBRANCE_GT PO_DISTS
WHERE REQ_DISTS.origin_sequence_num = PO_DISTS.sequence_num
AND PO_DISTS.distribution_type <> g_dist_type_REQUISITION
)
WHERE REQ_DISTS.distribution_type = g_dist_type_REQUISITION
AND REQ_DISTS.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT REQ_DISTS
SET REQ_DISTS.qty_open =
(SELECT
DECODE( greatest(0, REQ_DISTS.qty_ordered -
(PO_DISTS.qty_closed *
NVL(REQ_DISTS.uom_conversion_rate,1))
)
--Req qty < PO qty
--put (Req qty - PO billed qty) on Req
, 0 , (REQ_DISTS.qty_ordered -
( NVL(REQ_DISTS.uom_conversion_rate,1)
* (PO_DISTS.qty_closed
- p_ap_cancelled_qty) )
)
--if zero, Req qty > PO billed qty
--put entire cancelled qty on Req
, (p_ap_cancelled_qty
* NVL(REQ_DISTS.uom_conversion_rate,1))
)
FROM PO_ENCUMBRANCE_GT PO_DISTS
WHERE REQ_DISTS.origin_sequence_num = PO_DISTS.sequence_num
AND PO_DISTS.distribution_type <> g_dist_type_REQUISITION
) --end select
WHERE REQ_DISTS.distribution_type = g_dist_type_REQUISITION
AND REQ_DISTS.prevent_encumbrance_flag = 'N'
AND REQ_DISTS.amount_based_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT REQ_DISTS
SET REQ_DISTS.amt_open =
(SELECT
DECODE( REQ_DISTS.amount_based_flag
--quantity-based
, 'N', REQ_DISTS.qty_open * REQ_DISTS.price
, --amount based
DECODE( greatest(0, REQ_DISTS.amt_ordered -
PO_DISTS.amt_closed_func) --bug 3435571
--if zero, Req amt > PO billed amt
--put (Req amt - PO billed amt) on Req
, 0 , (REQ_DISTS.amt_ordered
- (PO_DISTS.amt_closed_func --bug 3435571
- p_ap_amt_billed_change)) -- bug 3480949: fixed parenthesis
--put entire cancelled amt on Req
--we will add tax to and round this amt
, p_ap_amt_billed_change
)
)
FROM PO_ENCUMBRANCE_GT PO_DISTS
WHERE REQ_DISTS.origin_sequence_num = PO_DISTS.sequence_num
AND PO_DISTS.distribution_type <> g_dist_type_REQUISITION
)
WHERE REQ_DISTS.distribution_type = g_dist_type_REQUISITION
AND REQ_DISTS.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT PPO_DISTS
SET PPO_DISTS.qty_open =
(SELECT SR_DISTS.qty_ordered - SR_DISTS.qty_closed
FROM PO_ENCUMBRANCE_GT SR_DISTS
WHERE SR_DISTS.sequence_num = PPO_DISTS.origin_sequence_num
)
WHERE PPO_DISTS.origin_sequence_num IS NOT NULL
AND PPO_DISTS.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT PPO_DISTS
SET PPO_DISTS.qty_open =
(SELECT
DECODE( greatest(0, SR_DISTS.qty_ordered -
SR_DISTS.qty_closed
)
--put difference between SR ord and SR billed
--back on PPO
, 0 , SR_DISTS.qty_ordered -
DECODE( p_action
, g_action_INVOICE_CANCEL,
SR_DISTS.quantity_billed
-- cr memo cancel
, SR_DISTS.quantity_billed +
p_ap_cancelled_qty
)
--put entire cancelled qty on PPO
, p_ap_cancelled_qty
)
FROM PO_ENCUMBRANCE_GT SR_DISTS
WHERE PPO_DISTS.origin_sequence_num = SR_DISTS.sequence_num
)
WHERE PPO_DISTS.origin_sequence_num IS NOT NULL
AND PPO_DISTS.prevent_encumbrance_flag = 'N'
;
'Updated qty_open for backing PPO'
);
UPDATE PO_ENCUMBRANCE_GT REQ_DISTS
SET REQ_DISTS.FUNDED_VALUE = por_amendment_pkg.getLastImplFundValue(REQ_DISTS.distribution_id)
WHERE REQ_DISTS.origin_sequence_num IS NOT NULL
AND REQ_DISTS.distribution_type = g_dist_type_REQUISITION
AND REQ_DISTS.CLM_DOC_FLAG = 'Y'
AND REQ_DISTS.prevent_encumbrance_flag = 'N';
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Successfully Updated the Fund Value for Backing Reqs.');
UPDATE PO_ENCUMBRANCE_GT PPO_DISTS
SET PPO_DISTS.amt_open =
PPO_DISTS.qty_open * PPO_DISTS.price
-- no Services lines on PPOs/SRs
WHERE PPO_DISTS.origin_sequence_num IS NOT NULL --backing document
AND PPO_DISTS.distribution_type = g_dist_type_PLANNED
AND PPO_DISTS.prevent_encumbrance_flag = 'N'
;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'CLM Installed. Calling UPDATE_CLM_INTIAL_AMOUNTS.');
UPDATE_CLM_INTIAL_AMOUNTS();
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Updated Initial Amounts for CLM Documents.');
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.amt_open =
DECODE( DISTS.amt_open
-- if NULL, then it needs to be calculated still
, NULL,
GREATEST( 0 , DISTS.amt_ordered - nvl(DISTS.amt_closed, 0))
-- already calculated (backing PPO), so do not overwrite
, GREATEST(0, DISTS.amt_open)
)
WHERE DISTS.prevent_encumbrance_flag = 'N'
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.amt_open =
GREATEST( 0,
DISTS.amt_open -
DECODE( DISTS.amount_based_flag
-- quantity_based:
, 'N', nvl(quantity_cancelled, 0) * nvl(DISTS.price,0)
-- Services line:
, nvl(amount_cancelled, 0)
)
)
WHERE DISTS.prevent_encumbrance_flag = 'N'
AND DISTS.distribution_type <>g_dist_type_AGREEMENT
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.amt_open = p_ap_reinstated_enc_amt
WHERE DISTS.prevent_encumbrance_flag = 'N'
AND DISTS.origin_sequence_num IS NULL -- main doc
;
'Updated amt_open for all lines'
);
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.pre_round_amt = (DISTS.amt_open * DISTS.nonrecoverable_tax_rate)
WHERE DISTS.prevent_encumbrance_flag = 'N'
AND DISTS.amt_open IS NOT NULL
;
SELECT num1,
num2
BULK COLLECT INTO
l_sequence_num_tbl,
l_enc_amt_tbl
FROM po_session_gt
WHERE index_char1= g_column_PO_ENCUMBERED_AMOUNT;
SELECT rowid BULK COLLECT INTO PO_DEBUG.g_rowid_tbl
FROM PO_SESSION_GT WHERE index_char1 = g_column_PO_ENCUMBERED_AMOUNT;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.pre_round_amt = DECODE (p_action ,g_action_RESERVE
,((DISTS.amt_ordered* DISTS.nonrecoverable_tax_rate )
- l_enc_amt_tbl(i))
,g_action_CANCEL
,l_enc_amt_tbl(i)-( DISTS.amt_closed*DISTS.nonrecoverable_tax_rate)
)
WHERE((p_action = g_action_RESERVE and Nvl(DISTS.amount_changed_flag,'N') = 'Y' ) or
p_action = g_action_CANCEL ) --Bug 16320071 Changed the where clause to fix cancel issue
AND (dists.clm_doc_flag = 'N')
AND dists.sequence_num = l_sequence_num_tbl(i);
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.pre_round_amt = (DISTS.amount_to_encumber - ( DISTS.encumbered_amount + DISTS.unencumbered_amount ))
WHERE Nvl(DISTS.amount_changed_flag,'N') = 'Y';
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.pre_round_amt = (DISTS.encumbered_amount)
WHERE DISTS.distribution_type = 'AGREEMENT' AND
(DISTS.clm_doc_flag = 'N');
'Updated pre_round_amt for all lines'
);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling UPDATE_CLM_FINAL_AMOUNTS');
UPDATE_CLM_FINAL_AMOUNTS(
p_action => p_action
, p_doc_type => p_doc_type
, p_doc_subtype => p_doc_subtype
, p_currency_code_func => p_currency_code_func
, p_min_acct_unit_func => p_min_acct_unit_func
, p_cur_precision_func => p_cur_precision_func
, p_ap_reinstated_enc_amt => p_ap_reinstated_enc_amt
, p_is_complex_work_po => p_is_complex_work_po --
);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Successfully Updated Final Amounts for CLM documents.');
SELECT
DISTS.sequence_num
, DECODE( p_column_to_use
, g_column_AMOUNT_TO_ENCUMBER, DISTS.amount_to_encumber
, g_column_AMT_CLOSED, DISTS.amt_closed
, g_column_PRE_ROUND_AMT, DISTS.pre_round_amt
, g_column_PO_ENCUMBERED_AMOUNT,DISTS.encumbered_amount
, NULL
)
-- bug 3568671: removed conditional setting of rate.
-- we now use the l_round_only_flag_tbl to indicate that
-- we do not want to do a currency conversion.
-- Bug 13503748 Encumbrance ER
-- Encumbered amount should be converted from functional to foreign currency
-- so passing inverse of rate value
, DECODE ( p_column_to_use ,g_column_PO_ENCUMBERED_AMOUNT , 1/NVL(DISTS.rate,1)
,DISTS.rate)
, DISTS.cur_precision_foreign
, DISTS.min_acct_unit_foreign
, p_cur_precision_func
, p_min_acct_unit_func
-- bug 3568671: For BPA distributions, currency conversion
-- should only occur for column to use = g_column_AMOUNT_TO_ENCUMBER.
-- We do this initial conversion for foreign currency BPAs, and then
-- all other calculated values are already in functional currency
-- (i.e amt_closed/pre_round_amt), however, there will still be a
-- foreign currency code and rate in the GTT. We do not want to repeat
-- the currency conversion a 2nd time. But rounding should occur for all
-- 3 of these columns.
-- Rounding without currency conversion is achieved by setting the
-- round_only_flag to 'Y' when calling PO_CORE_S2.round_and_convert_currency
, DECODE ( DISTS.distribution_type
, g_dist_type_AGREEMENT, DECODE( p_column_to_use
, g_column_AMOUNT_TO_ENCUMBER, 'N'
, 'Y'
)
, 'N'
)
-- Bug 3480949: capture origin_sequence_number
, DISTS.origin_sequence_num
BULK COLLECT INTO
l_sequence_num_tbl
, l_amount_to_round_tbl
, l_exchange_rate_tbl
, l_cur_precision_foreign_tbl
, l_min_acct_unit_foreign_tbl
, l_cur_precision_func_tbl
, l_min_acct_unit_func_tbl
, l_round_only_flag_tbl --bug 3568671
, l_origin_sequence_num_tbl -- bug 3480949
FROM PO_ENCUMBRANCE_GT DISTS
WHERE DISTS.prevent_encumbrance_flag = 'N'
ORDER BY DISTS.sequence_num
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET
amt_to_encumber_func =
DECODE( p_column_to_use
, g_column_AMOUNT_TO_ENCUMBER, l_amount_result_tbl(i)
, DISTS.amt_to_encumber_func
)
, amt_closed_func =
DECODE( p_column_to_use
, g_column_AMT_CLOSED, l_amount_result_tbl(i)
, DISTS.amt_closed_func
)
, final_amt =
DECODE( p_column_to_use
, g_column_PRE_ROUND_AMT, l_amount_result_tbl(i)
, DISTS.final_amt
)
WHERE DISTS.prevent_encumbrance_flag = 'N'
AND DISTS.sequence_num = l_sequence_num_tbl(i)
;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Inserting the converted encumbered amount into po session gt');
INSERT INTO po_session_gt(index_char1,num1,num2)
VALUES (g_column_PO_ENCUMBERED_AMOUNT,l_sequence_num_tbl(i),l_amount_result_tbl(i));
SELECT
DECODE( p_action
-- For Adjust, row is +/- depending on whether its new/old
, g_action_ADJUST, DECODE( PA_DISTS.adjustment_status
, g_adj_status_OLD, -1
, 1
)
-- Reserve, multiplier is always positive 1
, g_action_RESERVE, 1
-- Other actions are all reversals, so use -1
, -1
)
, PA_DISTS.distribution_id
, PA_DISTS.sequence_num
, PA_DISTS.amt_to_encumber_func
, PA_DISTS.unencumbered_amount
, PO_DISTS.final_amt
BULK COLLECT INTO
l_multiplier_tbl
, l_pa_dist_id_tbl
, l_pa_sequence_num_tbl
, l_amt_to_encumber_func_tbl
, l_unencumbered_amount_tbl
, l_amount_tbl
FROM
PO_ENCUMBRANCE_GT PA_DISTS
, PO_ENCUMBRANCE_GT PO_DISTS
WHERE PA_DISTS.origin_sequence_num = PO_DISTS.sequence_num
AND PA_DISTS.distribution_id = PO_DISTS.agreement_dist_id
AND PO_DISTS.prevent_encumbrance_flag = 'N'
AND PA_DISTS.distribution_type = g_dist_type_AGREEMENT
ORDER BY PA_DISTS.distribution_id, PO_DISTS.gl_encumbered_date DESC;
UPDATE PO_ENCUMBRANCE_GT PA_DISTS
SET PA_DISTS.final_amt = l_amount_tbl(i)
WHERE
PA_DISTS.distribution_id = l_pa_dist_id_tbl(i)
AND PA_DISTS.sequence_num = l_pa_sequence_num_tbl(i)
AND PA_DISTS.distribution_type = g_dist_type_AGREEMENT
;
'Updated of global temp table PA amounts'
);
UPDATE PO_ENCUMBRANCE_GT BACKING_REQ
SET BACKING_REQ.final_amt =
(SELECT MAIN_DOC.final_amt
FROM PO_ENCUMBRANCE_GT MAIN_DOC
WHERE MAIN_DOC.sequence_num = BACKING_REQ.origin_sequence_num
AND MAIN_DOC.origin_sequence_num IS NULL)
WHERE BACKING_REQ.origin_sequence_num IS NOT NULL
AND BACKING_REQ.distribution_type = g_dist_type_REQUISITION
AND BACKING_REQ.prevent_encumbrance_flag = 'N'
;
SELECT
BACKING_REQ.distribution_id
, MAX(CASE WHEN p_action = g_action_RESERVE
THEN BACKING_REQ.encumbered_amount
ELSE BACKING_REQ.amt_ordered --bug#5478754
END)
, SUM(BACKING_REQ.final_amt)
BULK COLLECT INTO
l_req_dist_id_tbl
, l_max_total_tbl
, l_req_dist_gtt_total_tbl
FROM PO_ENCUMBRANCE_GT BACKING_REQ
WHERE BACKING_REQ.origin_sequence_num IS NOT NULL
AND BACKING_REQ.distribution_type = g_dist_type_REQUISITION
AND BACKING_REQ.prevent_encumbrance_flag = 'N'
GROUP BY distribution_id
;
SELECT
sequence_num
, final_amt
BULK COLLECT INTO
l_sequence_num_tbl
, l_gtt_amount_tbl
FROM PO_ENCUMBRANCE_GT BACKING_REQ
WHERE BACKING_REQ.origin_sequence_num IS NOT NULL
AND BACKING_REQ.distribution_type = g_dist_type_REQUISITION
AND BACKING_REQ.prevent_encumbrance_flag = 'N'
AND BACKING_REQ.distribution_id = p_req_dist_id
ORDER BY distribution_num ASC
;
UPDATE PO_ENCUMBRANCE_GT BACKING_REQ
SET BACKING_REQ.final_amt = l_gtt_amount_tbl(i)
WHERE BACKING_REQ.sequence_num = l_sequence_num_tbl(i)
;
/* Start Bug 3292870: Split update of reference5 field off of query to make
* it compatible with an 8i db.
*/
UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
SET
ALL_DISTS.je_category_name =
DECODE( ALL_DISTS.distribution_type
, g_dist_type_REQUISITION, g_je_category_Requisitions
, g_je_category_Purchases
)
, ALL_DISTS.je_line_description =
DECODE( ALL_DISTS.distribution_type
, g_dist_type_AGREEMENT, ALL_DISTS.comments
, ALL_DISTS.item_description
)
, ALL_DISTS.encumbrance_type_id =
to_char( DECODE( ALL_DISTS.distribution_type
, g_dist_type_REQUISITION, p_req_encumb_type_id -- Reqs
, g_dist_type_AGREEMENT, p_req_encumb_type_id
, p_po_encumb_type_id
)
)
, ALL_DISTS.code_combination_id = ALL_DISTS.budget_account_id
, ALL_DISTS.reference1 =
DECODE( ALL_DISTS.distribution_type
, g_dist_type_REQUISITION, g_reference1_REQ
, g_dist_type_AGREEMENT, g_reference1_PA
, g_dist_type_SCHEDULED, g_reference1_REL
, g_dist_type_BLANKET, g_reference1_REL
, g_reference1_PO
)
-- bug 3404563: NULL out reference 2 and 3 if this is the
-- IP unsaved GMS Req case (identified by non-NULL award_num)
, ALL_DISTS.reference2 =
DECODE( ALL_DISTS.award_num
-- if null, then its a saved doc so use the id
, NULL, to_char(ALL_DISTS.header_id)
-- else means unsaved doc, so NULL out ref2
, NULL
)
, ALL_DISTS.reference3 =
DECODE( ALL_DISTS.award_num
-- if null, then its a saved doc so use the id
, NULL, to_char(ALL_DISTS.distribution_id)
-- else means unsaved doc, so NULL out ref3
, NULL
)
, ALL_DISTS.reference4 = ALL_DISTS.segment1
, ALL_DISTS.reference15 = to_char(ALL_DISTS.sequence_num)
WHERE ALL_DISTS.send_to_gl_flag = 'Y' --bug 3568512: use new column
;
UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
SET ALL_DISTS.reference5 = ALL_DISTS.reference_num
WHERE ALL_DISTS.send_to_gl_flag = 'Y' --bug 3568512: use new column
and ALL_DISTS.distribution_type = g_dist_type_REQUISITION
;
UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
SET ALL_DISTS.reference5 =
( SELECT PPO_DISTS.segment1
FROM PO_ENCUMBRANCE_GT PPO_DISTS
WHERE PPO_DISTS.origin_sequence_num
= ALL_DISTS.sequence_num
AND PPO_DISTS.distribution_id
= ALL_DISTS.source_distribution_id
)
WHERE ALL_DISTS.send_to_gl_flag = 'Y' --bug 3568512: use new column
and ALL_DISTS.distribution_type = g_dist_type_SCHEDULED
;
UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
SET ALL_DISTS.reference5 =
( SELECT PA_DISTS.segment1
FROM PO_ENCUMBRANCE_GT PA_DISTS
WHERE PA_DISTS.origin_sequence_num =
ALL_DISTS.sequence_num
AND PA_DISTS.distribution_id =
ALL_DISTS.agreement_dist_id
)
WHERE ALL_DISTS.send_to_gl_flag = 'Y' --bug 3568512: use new column
and ALL_DISTS.distribution_type NOT IN (g_dist_type_SCHEDULED, g_dist_type_REQUISITION)
RETURNING ROWIDTOCHAR(rowid) BULK COLLECT into l_rowid_char_tbl
;
UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
SET ALL_DISTS.reference5 =
( SELECT REQ_DISTS.segment1
FROM PO_ENCUMBRANCE_GT REQ_DISTS
WHERE REQ_DISTS.origin_sequence_num =
ALL_DISTS.sequence_num
AND REQ_DISTS.distribution_type =
g_dist_type_REQUISITION
)
WHERE ALL_DISTS.send_to_gl_flag = 'Y' --bug 3568512: use new column
and rowid = CHARTOROWID(l_rowid_char_tbl(i))
and ALL_DISTS.reference5 IS NULL
;
UPDATE PO_ENCUMBRANCE_GT BACKING
SET
BACKING.reference6 = g_reference6_SRCDOC
, BACKING.reference10 =
(
SELECT TO_CHAR(MAIN.header_id)
FROM PO_ENCUMBRANCE_GT MAIN
WHERE MAIN.sequence_num = BACKING.origin_sequence_num
)
WHERE BACKING.origin_sequence_num IS NOT NULL --backing doc
AND BACKING.send_to_gl_flag = 'Y' --bug 3568512: use new column
;
UPDATE PO_ENCUMBRANCE_GT MAIN
SET
MAIN.reference6 = g_reference6_SRCDOC
, MAIN.reference10 = l_source_doc_reference
WHERE MAIN.send_to_gl_flag = 'Y' --bug 3568512: use new column
AND MAIN.origin_sequence_num IS NULL
;
UPDATE PO_ENCUMBRANCE_GT MAIN
SET
MAIN.reference6 = g_reference6_GMSIP
, MAIN.reference7 = to_char(project_id)
, MAIN.reference8 = to_char(task_id)
, MAIN.reference9 = award_num
, MAIN.reference10 = expenditure_type
, MAIN.reference11 = to_char(expenditure_organization_id)
, MAIN.reference12 = expenditure_item_date /* Bug 3081539 */
, MAIN.reference13 = to_char(vendor_id)
WHERE MAIN.award_num IS NOT NULL --identifies the unsaved Req case
AND MAIN.send_to_gl_flag = 'Y' --bug 3568512: use new column
AND MAIN.origin_sequence_num IS NULL
;
UPDATE PO_ENCUMBRANCE_GT BACKING_REQ
SET reference14 =
(SELECT REQ_TABLE.source_req_distribution_id
FROM PO_REQ_DISTRIBUTIONS_ALL REQ_TABLE
WHERE BACKING_REQ.distribution_id = REQ_TABLE.distribution_id
)
WHERE BACKING_REQ.origin_sequence_num IS NOT NULL
AND BACKING_REQ.distribution_type = g_dist_type_REQUISITION
AND BACKING_REQ.project_id IS NOT NULL
;
UPDATE PO_ENCUMBRANCE_GT MAIN_REQ
SET reference14 =
(SELECT PARENT_DIST.distribution_id
FROM PO_REQ_DISTRIBUTIONS_ALL PARENT_DIST
, PO_REQUISITION_LINES_ALL PARENT_LINE
, PO_REQUISITION_LINES_ALL CHILD_LINE
WHERE MAIN_REQ.line_id = CHILD_LINE.requisition_line_id
AND PARENT_LINE.requisition_line_id = CHILD_LINE.parent_req_line_id
AND PARENT_DIST.requisition_line_id = PARENT_LINE.requisition_line_id
AND MAIN_REQ.distribution_num = PARENT_DIST.distribution_num
-- Bug9663871 NEW JOIN CONDITION ON DIST NUM
)
WHERE MAIN_REQ.origin_sequence_num IS NULL
AND MAIN_REQ.distribution_type = g_dist_type_REQUISITION
AND MAIN_REQ.adjustment_status = g_adj_status_NEW
AND MAIN_REQ.project_id IS NOT NULL
;
SELECT POLC.description
INTO l_cbc_line_description
FROM PO_LOOKUP_CODES POLC
WHERE POLC.lookup_type = 'CONTROL ACTIONS'
AND POLC.lookup_code = l_cbc_action
;
UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
SET
ALL_DISTS.je_line_description =
SUBSTRB(ALL_DISTS.je_line_description,1,100)
|| '-'
|| SUBSTRB(l_cbc_line_description,1,139)
WHERE ALL_DISTS.send_to_gl_flag = 'Y'
;
PROCEDURE delete_encumbrance_gt
IS
l_proc_name CONSTANT VARCHAR2(30) := 'DELETE_ENCUMBRANCE_GT';
DELETE FROM PO_ENCUMBRANCE_GT ;
END delete_encumbrance_gt;
PROCEDURE update_amounts
(
p_action IN VARCHAR2,
p_currency_code_func IN VARCHAR2
)
IS
l_api_name CONSTANT varchar2(40) := 'UPDATE_AMOUNTS';
'Update for '|| p_action
);
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.entered_amount = decode(DISTS.distribution_type,g_dist_type_AGREEMENT,
DISTS.final_amt,g_dist_type_REQUISITION,DISTS.final_amt,
DISTS.pre_round_amt)
,DISTS.accounted_amount = DISTS.final_amt
WHERE DISTS.prevent_encumbrance_flag = 'N' ;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.send_to_gl_flag = 'N'
WHERE DISTS.amount_changed_flag = 'Y'
AND Nvl(DISTS.entered_amount,0) = 0;
UPDATE po_distributions_all POD
SET POD.amount_changed_flag = NULL
WHERE po_distribution_id IN
(SELECT distribution_id
FROM PO_ENCUMBRANCE_GT DISTS
WHERE DISTS.amount_changed_flag = 'Y'
AND Nvl(DISTS.entered_amount,0) = 0);
'Update for Cancel action'
);
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.entered_amount = -1 *decode(DISTS.distribution_type,g_dist_type_AGREEMENT,
DISTS.final_amt,g_dist_type_REQUISITION,DISTS.final_amt,
DISTS.pre_round_amt)
,DISTS.accounted_amount = -1 * DISTS.final_amt
WHERE DISTS.prevent_encumbrance_flag = 'N';
SELECT
FND_CUR.minimum_accountable_unit
, FND_CUR.precision
INTO
l_min_acct_unit_func
, l_cur_precision_func
FROM FND_CURRENCIES FND_CUR
WHERE FND_CUR.currency_code = p_currency_code_func
;
SELECT
DISTS.sequence_num
, DISTS.entered_amount
, decode(DISTS.distribution_type,g_dist_type_AGREEMENT,1/nvl(DISTS.rate,1),1)
, decode(DISTS.distribution_type,g_dist_type_AGREEMENT,
l_cur_precision_func,DISTS.cur_precision_foreign)
, decode(DISTS.distribution_type,g_dist_type_AGREEMENT,
l_min_acct_unit_func,DISTS.min_acct_unit_foreign)
, nvl(DISTS.cur_precision_foreign,l_cur_precision_func)
, nvl(DISTS.min_acct_unit_foreign,l_min_acct_unit_func)
, decode(DISTS.distribution_type,g_dist_type_AGREEMENT,'N','Y') --round only flag.
, DISTS.origin_sequence_num
BULK COLLECT INTO
l_sequence_num_tbl
, l_amount_to_round_tbl
, l_exchange_rate_tbl
, l_cur_precision_from_tbl
, l_min_acct_unit_from_tbl
, l_cur_precision_to_tbl
, l_min_acct_unit_to_tbl
, l_round_only_flag_tbl
, l_origin_sequence_num_tbl
FROM PO_ENCUMBRANCE_GT DISTS
WHERE DISTS.prevent_encumbrance_flag = 'N'
AND DISTS.distribution_type <> g_dist_type_REQUISITION --
ORDER BY DISTS.sequence_num
;
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.entered_amount= l_amount_result_tbl(i)
WHERE DISTS.prevent_encumbrance_flag = 'N'
AND DISTS.sequence_num = l_sequence_num_tbl(i)
;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'After completing update on po_encumbrance_gt for distributions');
This need not be done any longer as FV now handles zero dollar values.Commenting out the update*/
/*If (po_partial_funding_pkg.is_clm_enabled = 'Y' and p_action = g_action_RESERVE) Then
l_progress := '105';
UPDATE PO_ENCUMBRANCE_GT CLM_DISTS
SET CLM_DISTS.SEND_TO_GL_FLAG = 'N'
WHERE (CLM_DISTS.CLM_DOC_FLAG = 'Y'
AND CLM_DISTS.ENTERED_AMOUNT = 0
AND CLM_DISTS.ACCOUNTED_AMOUNT = 0);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Successfully Updated the Send To Gl Flag for the Candidate CLM Distributions with Zero Amounts.');
END update_amounts;
PROCEDURE UPDATE_CLM_INTIAL_AMOUNTS
IS
l_proc_name CONSTANT VARCHAR2(30) := 'UPDATE_CLM_INTIAL_AMOUNTS';
UPDATE PO_ENCUMBRANCE_GT CLM_DISTS
SET CLM_DISTS.AMT_ORDERED = DECODE(CLM_DISTS.amount_based_flag,
'N', NVL(CLM_DISTS.QUANTITY_FUNDED,0) * NVL(CLM_DISTS.PRICE,0) ,
NVL(CLM_DISTS.AMOUNT_FUNDED,0))
WHERE CLM_DISTS.CLM_DOC_FLAG = 'Y'
and CLM_DISTS.prevent_encumbrance_flag = 'N';
PO_DEBUG.debug_stmt(l_log_head, l_progress, 'Updated Amt Ordered for CLM Distributions.');
END UPDATE_CLM_INTIAL_AMOUNTS;
Procedure to calculate and update the Final Amount on the PO Encumbrance Gt for CLM documents in case of Reserve Action
*/
PROCEDURE UPDATE_CLM_FINAL_AMOUNTS
(
p_action IN VARCHAR2 ,
p_doc_type IN VARCHAR2 ,
p_doc_subtype IN VARCHAR2 ,
p_currency_code_func IN VARCHAR2 ,
p_min_acct_unit_func IN NUMBER ,
p_cur_precision_func IN NUMBER ,
p_ap_reinstated_enc_amt IN NUMBER ,
p_is_complex_work_po IN BOOLEAN
)
IS
l_api_name CONSTANT VARCHAR2(40) := 'UPDATE_CLM_FINAL_AMOUNTS';
UPDATE PO_ENCUMBRANCE_GT MAIN_DISTS
SET MAIN_DISTS.PRE_ROUND_AMT = (MAIN_DISTS.CHANGE_IN_FUNDED_VALUE/MAIN_DISTS.RATE),
MAIN_DISTS.FINAL_AMT = MAIN_DISTS.CHANGE_IN_FUNDED_VALUE
WHERE MAIN_DISTS.ORIGIN_SEQUENCE_NUM IS NULL
AND MAIN_DISTS.CLM_DOC_FLAG = 'Y'
AND MAIN_DISTS.PREVENT_ENCUMBRANCE_FLAG = 'N';
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Updated Final Amount for Main Document Distributions');
UPDATE PO_ENCUMBRANCE_GT BACK_REQ
SET (BACK_REQ.PRE_ROUND_AMT, BACK_REQ.FINAL_AMT) =
(SELECT MAIN_DISTS.PRE_ROUND_AMT, MAIN_DISTS.FINAL_AMT
FROM PO_ENCUMBRANCE_GT MAIN_DISTS
WHERE MAIN_DISTS.ORIGIN_SEQUENCE_NUM IS NULL
AND MAIN_DISTS.CLM_DOC_FLAG = 'Y'
AND MAIN_DISTS.PREVENT_ENCUMBRANCE_FLAG = 'N'
AND MAIN_DISTS.REQ_DISTRIBUTION_ID = BACK_REQ.DISTRIBUTION_ID
AND MAIN_DISTS.SEQUENCE_NUM = BACK_REQ.ORIGIN_SEQUENCE_NUM --bug 13823506
)
/* Line Type Changes: Reserve action on the Award with backing Requisition should
relieve commitments equal to the obligation created */
WHERE BACK_REQ.ORIGIN_SEQUENCE_NUM IS NOT NULL
AND BACK_REQ.CLM_DOC_FLAG = 'Y'
AND BACK_REQ.DISTRIBUTION_TYPE = g_dist_type_REQUISITION
AND BACK_REQ.PREVENT_ENCUMBRANCE_FLAG = 'N';
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Updated Final Amount for Backing Requisition Distributions');
UPDATE PO_ENCUMBRANCE_GT MAIN_DISTS
SET (MAIN_DISTS.CHANGE_IN_FUNDED_VALUE, MAIN_DISTS.PRE_ROUND_AMT, MAIN_DISTS.FINAL_AMT)
= (SELECT NVL(PCDD.FUNDS_REMAINING,0), NVL(PCDD.FUNDS_REMAINING,0), NVL(PCDD.FUNDS_REMAINING,0)
FROM PO_CLMREQ_DIST_DETAILS_V PCDD
WHERE PCDD.DISTRIBUTION_ID = MAIN_DISTS.DISTRIBUTION_ID
AND NVL(PCDD.IS_FUNDS_REMAINING,'N') = 'Y')
WHERE MAIN_DISTS.ORIGIN_SEQUENCE_NUM IS NULL
AND MAIN_DISTS.CLM_DOC_FLAG = 'Y'
AND NVL(MAIN_DISTS.CHANGE_IN_FUNDED_VALUE,0) = 0
AND MAIN_DISTS.ENCUMBERED_FLAG = 'Y'
AND MAIN_DISTS.PREVENT_ENCUMBRANCE_FLAG = 'N';
SELECT CLM_DISTS.sequence_num
,CLM_DISTS.FINAL_AMT
,CLM_DISTS.rate
,CLM_DISTS.cur_precision_foreign
,CLM_DISTS.min_acct_unit_foreign
,p_cur_precision_func
,p_min_acct_unit_func
,'Y'
,CLM_DISTS.origin_sequence_num
BULK COLLECT INTO
l_sequence_num_tbl
,l_amount_to_round_tbl
,l_exchange_rate_tbl
,l_cur_precision_foreign_tbl
,l_min_acct_unit_foreign_tbl
,l_cur_precision_func_tbl
,l_min_acct_unit_func_tbl
,l_round_only_flag_tbl
,l_origin_sequence_num_tbl
FROM PO_ENCUMBRANCE_GT CLM_DISTS
WHERE CLM_DISTS.prevent_encumbrance_flag = 'N'
AND CLM_DISTS.CLM_DOC_FLAG = 'Y'
ORDER BY CLM_DISTS.sequence_num;
UPDATE PO_ENCUMBRANCE_GT CLM_DISTS
SET CLM_DISTS.final_amt = l_amount_result_tbl(i)
WHERE CLM_DISTS.prevent_encumbrance_flag = 'N'
AND CLM_DISTS.sequence_num = l_sequence_num_tbl(i) ;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Updated the Rounded Values back for CLM Distributions');
END UPDATE_CLM_FINAL_AMOUNTS;