[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT period_year
FROM gl_periods gp,
gl_sets_of_books gsob
WHERE gp.period_set_name = gsob.period_set_name
AND gp.period_type = gsob.accounted_period_type
AND trunc(p_date) BETWEEN trunc(gp.start_date)
AND trunc(gp.end_date)
AND gsob.set_of_books_id = p_sob_id;
Procedure Insert_Interface_Row
=================================================================================*/
/* Inserts row into budgetary control interface table */
PROCEDURE Insert_Interface_Row(p_cc_interface_rec IN igc_cc_interface%ROWTYPE)
IS
l_full_path VARCHAR2(255);
l_full_path := g_path || 'Insert_Interface_Row';
INSERT INTO igc_cc_interface (
batch_line_num,
cc_header_id,
cc_version_num,
cc_acct_line_id,
cc_det_pf_line_id,
set_of_books_id,
code_combination_id,
cc_transaction_date,
transaction_description,
encumbrance_type_id,
currency_code,
cc_func_dr_amt,
cc_func_cr_amt,
je_source_name,
je_category_name,
actual_flag,
budget_dest_flag,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
period_set_name,
period_name,
cbc_result_code,
status_code,
budget_version_id,
budget_amt,
commitment_encmbrnc_amt,
obligation_encmbrnc_amt,
funds_available_amt,
document_type,
reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
reference_6,
reference_7,
reference_8,
reference_9,
reference_10,
cc_encmbrnc_date,
/* Bug No : 6341012. SLA uptake. Event_ID, Project_Line are added to IGC_CC_INTERFACE Table */
event_id,
project_line)
VALUES
(p_cc_interface_rec.batch_line_num,
p_cc_interface_rec.cc_header_id,
p_cc_interface_rec.cc_version_num,
p_cc_interface_rec.cc_acct_line_id,
p_cc_interface_rec.cc_det_pf_line_id,
p_cc_interface_rec.set_of_books_id,
p_cc_interface_rec.code_combination_id,
p_cc_interface_rec.cc_transaction_date,
p_cc_interface_rec.transaction_description,
p_cc_interface_rec.encumbrance_type_id,
p_cc_interface_rec.currency_code,
p_cc_interface_rec.cc_func_dr_amt,
p_cc_interface_rec.cc_func_cr_amt,
p_cc_interface_rec.je_source_name,
p_cc_interface_rec.je_category_name,
p_cc_interface_rec.actual_flag,
p_cc_interface_rec.budget_dest_flag,
p_cc_interface_rec.last_update_date,
p_cc_interface_rec.last_updated_by,
p_cc_interface_rec.last_update_login,
p_cc_interface_rec.creation_date,
p_cc_interface_rec.created_by,
p_cc_interface_rec.period_set_name,
p_cc_interface_rec.period_name,
p_cc_interface_rec.cbc_result_code,
p_cc_interface_rec.status_code,
p_cc_interface_rec.budget_version_id,
p_cc_interface_rec.budget_amt,
p_cc_interface_rec.commitment_encmbrnc_amt,
p_cc_interface_rec.obligation_encmbrnc_amt,
p_cc_interface_rec.funds_available_amt,
p_cc_interface_rec.document_type,
p_cc_interface_rec.reference_1,
p_cc_interface_rec.reference_2,
p_cc_interface_rec.reference_3,
p_cc_interface_rec.reference_4,
p_cc_interface_rec.reference_5,
p_cc_interface_rec.reference_6,
p_cc_interface_rec.reference_7,
p_cc_interface_rec.reference_8,
p_cc_interface_rec.reference_9,
p_cc_interface_rec.reference_10,
p_cc_interface_rec.cc_encmbrnc_date,
/* Bug No : 6341012. SLA uptake. Event_ID, Project_Line are added to IGC_CC_INTERFACE Table */
p_cc_interface_rec.event_id,
p_cc_interface_rec.project_line);
END Insert_Interface_Row;
l_cc_interface_rec.last_update_date := sysdate;
l_cc_interface_rec.last_updated_by := -1;
l_cc_interface_rec.last_update_login := -1;
Insert_Interface_Row(l_cc_interface_rec);
e_delete EXCEPTION;
-- SELECT *
-- FROM igc_cc_det_pf_v
-- WHERE cc_acct_line_id = t_cc_acct_line_id;
SELECT ccdpf.ROWID,
ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
NULL cc_acct_line_num,
ccdpf.cc_acct_line_id,
NULL parent_det_pf_line_num,
ccdpf.parent_det_pf_line_id,
ccdpf.parent_acct_line_id,
ccdpf.cc_det_pf_entered_amt,
ccdpf.cc_det_pf_func_amt,
ccdpf.cc_det_pf_date,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
ccdpf.cc_det_pf_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
ccdpf.cc_det_pf_encmbrnc_amt,
( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
ccdpf.cc_det_pf_encmbrnc_date,
ccdpf.cc_det_pf_encmbrnc_status,
ccdpf.context,
ccdpf.attribute1,
ccdpf.attribute2,
ccdpf.attribute3,
ccdpf.attribute4,
ccdpf.attribute5,
ccdpf.attribute6,
ccdpf.attribute7,
ccdpf.attribute8,
ccdpf.attribute9,
ccdpf.attribute10,
ccdpf.attribute11,
ccdpf.attribute12,
ccdpf.attribute13,
ccdpf.attribute14,
ccdpf.attribute15,
ccdpf.last_update_date,
ccdpf.last_updated_by,
ccdpf.last_update_login,
ccdpf.creation_date,
ccdpf.created_by
FROM igc_cc_det_pf ccdpf
WHERE cc_acct_line_id = t_cc_acct_line_id;
SELECT *
FROM igc_cc_acct_lines ccac
WHERE ccac.cc_header_id = t_cc_header_id;
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
/* Delete existing interface rows */
BEGIN
DELETE igc_cc_interface
WHERE cc_header_id = p_cc_header_id AND
actual_flag = 'E';
SELECT a.cc_det_pf_line_id
INTO l_cc_det_pf_line_id
FROM igc_cc_det_pf a
WHERE
a.cc_det_pf_line_num = (SELECT NVL(min(b.cc_det_pf_line_num) , -1)
FROM igc_cc_det_pf b
WHERE b.cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id AND
b.cc_det_pf_date = /* bug fix 1702768 */
(SELECT min(c.cc_det_pf_date)
FROM igc_cc_det_pf c
WHERE c.cc_acct_line_id =
l_cc_acct_lines_rec.cc_acct_line_id AND
c.cc_det_pf_date >= p_target_date)
) AND
a.cc_det_pf_date >= p_target_date AND
a.cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
-- SELECT *
-- INTO l_cc_pmt_fcst_rec
-- FROM igc_cc_det_pf_v
-- WHERE cc_det_pf_line_id = l_cc_det_pf_line_id;
SELECT ccdpf.ROWID,
ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
NULL cc_acct_line_num,
ccdpf.cc_acct_line_id,
NULL parent_det_pf_line_num,
ccdpf.parent_det_pf_line_id,
ccdpf.parent_acct_line_id,
ccdpf.cc_det_pf_entered_amt,
ccdpf.cc_det_pf_func_amt,
ccdpf.cc_det_pf_date,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
ccdpf.cc_det_pf_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
ccdpf.cc_det_pf_encmbrnc_amt,
( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
ccdpf.cc_det_pf_encmbrnc_date,
ccdpf.cc_det_pf_encmbrnc_status,
ccdpf.context,
ccdpf.attribute1,
ccdpf.attribute2,
ccdpf.attribute3,
ccdpf.attribute4,
ccdpf.attribute5,
ccdpf.attribute6,
ccdpf.attribute7,
ccdpf.attribute8,
ccdpf.attribute9,
ccdpf.attribute10,
ccdpf.attribute11,
ccdpf.attribute12,
ccdpf.attribute13,
ccdpf.attribute14,
ccdpf.attribute15,
ccdpf.last_update_date,
ccdpf.last_updated_by,
ccdpf.last_update_login,
ccdpf.creation_date,
ccdpf.created_by
INTO l_cc_pmt_fcst_rec
FROM igc_cc_det_pf ccdpf
WHERE cc_det_pf_line_id = l_cc_det_pf_line_id;
-- SELECT NVL(SUM(NVL(cc_det_pf_func_amt,0) - NVL(cc_det_pf_func_billed_amt,0)) ,0)
SELECT NVL(SUM(NVL(a.cc_det_pf_func_amt,0) -
NVL(IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(a.cc_det_pf_line_id, a.cc_det_pf_line_num, a.cc_acct_line_id),0)) ,0)
INTO l_enc_amt
FROM igc_cc_det_pf a
WHERE a.cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id
-- The check for det_pf_line is not correct.The amount calculated should not be from
-- the target payment forecast. Bug 2858425, 19 March 2003
-- AND cc_det_pf_line_id = l_cc_det_pf_line_id
AND a.cc_det_pf_date >= p_start_date AND a.cc_det_pf_date <= p_end_date;
SELECT count(*)
INTO l_interface_row_count
FROM igc_cc_interface
WHERE cc_header_id = p_cc_header_id;
SELECT gps.closing_status
INTO l_period_status
FROM gl_period_statuses gps,
gl_periods gp,
gl_sets_of_books gb,
gl_period_types gpt,
fnd_application fa
WHERE
gb.set_of_books_id = p_sob_id1 AND
gp.period_set_name = gb.period_set_name AND
gp.period_type = gb.accounted_period_type AND
gp.adjustment_period_flag = 'N' AND
gpt.period_type = gp.period_type AND
gps.set_of_books_id = gb.set_of_books_id AND
gps.period_name = gp.period_name AND
gps.application_id = fa.application_id AND
fa.application_short_name = 'SQLGL' AND
(gp.start_date <= p_date_to_check AND gp.end_date >= p_date_to_check);
SELECT ccp.cc_period_status
INTO l_cc_period_status
FROM igc_cc_periods ccp,
gl_periods gp ,
gl_sets_of_books gb
WHERE
ccp.period_set_name = gp.period_set_name AND
gp.period_set_name = gb.period_set_name AND
ccp.org_id = p_org_id AND
ccp.period_name = gp.period_name AND
gp.period_type = gb.accounted_period_type AND
gp.adjustment_period_flag = 'N' AND
gb.set_of_books_id = p_sob_id2 AND
(gp.start_date <= p_date_to_check AND gp.end_date >= p_date_to_check);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_TG_NOT_IN_SELECT_DT');
/* This Function returns 'P' if the Payment Forecast Update is successfull for the cc_header_id passed.
If the update is failed then it returns 'F' along with the error_message */
/*==================================================================================
Function MPFS_UPDATE
=================================================================================*/
FUNCTION MPFS_UPDATE (p_cc_header_id IN NUMBER,
p_request_id IN NUMBER,
p_sob_id IN NUMBER,
p_org_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_target_date IN DATE,
p_transfer_date IN DATE,
l_error_message OUT NOCOPY VARCHAR2
)
RETURN VARCHAR2
IS
l_cc_headers_rec igc_cc_headers%ROWTYPE;
SELECT ccal.ROWID,
ccal.cc_header_id,
NULL org_id,
NULL cc_type,
NULL cc_type_code,
NULL cc_num,
ccal.cc_acct_line_id,
ccal.cc_acct_line_num,
ccal.cc_acct_desc,
ccal.parent_header_id,
ccal.parent_acct_line_id,
NULL parent_cc_acct_line_num,
NULL cc_budget_acct_desc,
ccal.cc_budget_code_combination_id,
NULL cc_charge_acct_desc,
ccal.cc_charge_code_combination_id,
ccal.cc_acct_entered_amt,
ccal.cc_acct_func_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_func_billed_amt,
ccal.cc_acct_encmbrnc_amt,
( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccal.cc_acct_entered_amt,0) ) - NVL(ccal.cc_acct_encmbrnc_amt,0) ) cc_acct_unencmrd_amt,
ccal.cc_acct_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id, NVL(ccal.cc_acct_entered_amt,0)) cc_acct_comp_func_amt,
NULL project_number ,
ccal.project_id,
NULL task_number,
ccal.task_id,
ccal.expenditure_type,
NULL expenditure_org_name,
ccal.expenditure_org_id,
ccal.expenditure_item_date,
ccal.cc_acct_taxable_flag,
NULL tax_name,
ccal.tax_id,
ccal.cc_acct_encmbrnc_status,
ccal.cc_acct_encmbrnc_date,
ccal.context,
ccal.attribute1,
ccal.attribute2,
ccal.attribute3,
ccal.attribute4,
ccal.attribute5,
ccal.attribute6,
ccal.attribute7,
ccal.attribute8,
ccal.attribute9,
ccal.attribute10,
ccal.attribute11,
ccal.attribute12,
ccal.attribute13,
ccal.attribute14,
ccal.attribute15,
ccal.created_by,
ccal.creation_date,
ccal.last_updated_by,
ccal.last_update_date,
ccal.last_update_login,
ccal.cc_func_withheld_amt,
ccal.cc_ent_withheld_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id, NVL(ccal.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
ccal.tax_classif_code -- modified for Ebtax uptake (Bug No-6472296)
FROM igc_cc_acct_lines ccal
WHERE ccal.cc_header_id = p_cc_header_id
AND exists ( SELECT 'x'
FROM igc_cc_det_pf ccdpf
WHERE ccdpf.cc_acct_line_id = ccal.cc_acct_line_id
AND ccdpf.cc_det_pf_date >= p_start_date
AND ccdpf.cc_det_pf_date <= p_end_date
AND (ccdpf.cc_det_pf_func_amt -
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id)) >0);
SELECT ccdpf.ROWID,
ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
NULL cc_acct_line_num,
ccdpf.cc_acct_line_id,
NULL parent_det_pf_line_num,
ccdpf.parent_det_pf_line_id,
ccdpf.parent_acct_line_id,
ccdpf.cc_det_pf_entered_amt,
ccdpf.cc_det_pf_func_amt,
ccdpf.cc_det_pf_date,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
ccdpf.cc_det_pf_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
ccdpf.cc_det_pf_encmbrnc_amt,
( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
ccdpf.cc_det_pf_encmbrnc_date,
ccdpf.cc_det_pf_encmbrnc_status,
ccdpf.context,
ccdpf.attribute1,
ccdpf.attribute2,
ccdpf.attribute3,
ccdpf.attribute4,
ccdpf.attribute5,
ccdpf.attribute6,
ccdpf.attribute7,
ccdpf.attribute8,
ccdpf.attribute9,
ccdpf.attribute10,
ccdpf.attribute11,
ccdpf.attribute12,
ccdpf.attribute13,
ccdpf.attribute14,
ccdpf.attribute15,
ccdpf.last_update_date,
ccdpf.last_updated_by,
ccdpf.last_update_login,
ccdpf.creation_date,
ccdpf.created_by
FROM igc_cc_det_pf ccdpf
WHERE ccdpf.cc_acct_line_id = p_cc_acct_line_id
AND ccdpf.cc_det_pf_date >= p_start_date
AND ccdpf.cc_det_pf_date <= p_end_date
AND (ccdpf.cc_det_pf_func_amt-IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id)) >0;
SELECT sum(ccdpf.cc_det_pf_func_amt-
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id)) func_amt_shift,
sum(ccdpf.cc_det_pf_entered_amt -
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id)) amt_shift
FROM igc_cc_det_pf ccdpf
WHERE ccdpf.cc_acct_line_id = p_cc_acct_line_id
AND ccdpf.cc_det_pf_date >= p_start_date
AND ccdpf.cc_det_pf_date <= p_end_date
AND (ccdpf.cc_det_pf_func_amt -
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id)) >0;
SELECT ccdpf.ROWID,
ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
NULL cc_acct_line_num,
ccdpf.cc_acct_line_id,
NULL parent_det_pf_line_num,
ccdpf.parent_det_pf_line_id,
ccdpf.parent_acct_line_id,
ccdpf.cc_det_pf_entered_amt,
ccdpf.cc_det_pf_func_amt,
ccdpf.cc_det_pf_date,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
ccdpf.cc_det_pf_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
ccdpf.cc_det_pf_encmbrnc_amt,
( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
ccdpf.cc_det_pf_encmbrnc_date,
ccdpf.cc_det_pf_encmbrnc_status,
ccdpf.context,
ccdpf.attribute1,
ccdpf.attribute2,
ccdpf.attribute3,
ccdpf.attribute4,
ccdpf.attribute5,
ccdpf.attribute6,
ccdpf.attribute7,
ccdpf.attribute8,
ccdpf.attribute9,
ccdpf.attribute10,
ccdpf.attribute11,
ccdpf.attribute12,
ccdpf.attribute13,
ccdpf.attribute14,
ccdpf.attribute15,
ccdpf.last_update_date,
ccdpf.last_updated_by,
ccdpf.last_update_login,
ccdpf.creation_date,
ccdpf.created_by
FROM igc_cc_det_pf ccdpf
WHERE ccdpf.cc_acct_line_id = p_cc_acct_line_id
AND ccdpf.cc_det_pf_date >= p_target_date
AND ccdpf.cc_det_pf_line_num =
(SELECT min(ccdpf1.cc_det_pf_line_num)
FROM igc_cc_det_pf ccdpf1
WHERE ccdpf1.cc_acct_line_id = p_cc_acct_line_id
AND ccdpf1.cc_det_pf_date = /* bug fix 1702768 */
(SELECT MIN(ccdpf2.cc_det_pf_date)
FROM igc_cc_det_pf ccdpf2
WHERE ccdpf2.cc_acct_line_id = p_cc_acct_line_id
AND ccdpf2.cc_det_pf_date >= p_target_date)
);
l_Last_Updated_By NUMBER;
l_Last_Update_Login NUMBER;
l_Last_Updated_By := FND_GLOBAL.USER_ID;
l_Last_Update_Login := FND_GLOBAL.LOGIN_ID;
l_full_path := g_path || 'MPFS_UPDATE';
Output_Debug (l_full_path, ' IGCCMPSB -- ************ Begin MPFS_UPDATE '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id
FOR UPDATE NOWAIT;
SELECT old_approval_status
INTO l_approval_status
FROM igc_cc_process_data
WHERE cc_header_id = p_cc_header_id AND
request_id = p_request_id ;
/* Update PF Line History */
l_return_status := FND_API.G_RET_STS_SUCCESS;
IGC_CC_DET_PF_HISTORY_PKG.Insert_Row(
l_api_version,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_hist_pf_row_id,
l_cc_pf_target.CC_Det_PF_Line_Id,
l_cc_pf_target.CC_Det_PF_Line_Num,
l_cc_pf_target.CC_Acct_Line_Id,
l_cc_pf_target.Parent_Acct_Line_Id,
l_cc_pf_target.Parent_Det_PF_Line_Id,
l_cc_headers_rec.cc_version_num,
'U',
l_cc_pf_target.CC_Det_PF_Entered_Amt,
l_cc_pf_target.CC_Det_PF_Func_Amt,
l_cc_pf_target.CC_Det_PF_Date,
l_cc_pf_target.CC_Det_PF_Billed_Amt,
l_cc_pf_target.CC_Det_PF_Unbilled_Amt,
l_cc_pf_target.CC_Det_PF_Encmbrnc_Amt,
l_cc_pf_target.CC_Det_PF_Encmbrnc_Date,
l_cc_pf_target.CC_Det_PF_Encmbrnc_Status,
l_cc_pf_target.Last_Update_Date,
l_cc_pf_target.Last_Updated_By,
l_cc_pf_target.Last_Update_Login,
l_cc_pf_target.Creation_Date,
l_cc_pf_target.Created_By,
l_cc_pf_target.Attribute1,
l_cc_pf_target.Attribute2,
l_cc_pf_target.Attribute3,
l_cc_pf_target.Attribute4,
l_cc_pf_target.Attribute5,
l_cc_pf_target.Attribute6,
l_cc_pf_target.Attribute7,
l_cc_pf_target.Attribute8,
l_cc_pf_target.Attribute9,
l_cc_pf_target.Attribute10,
l_cc_pf_target.Attribute11,
l_cc_pf_target.Attribute12,
l_cc_pf_target.Attribute13,
l_cc_pf_target.Attribute14,
l_cc_pf_target.Attribute15,
l_cc_pf_target.Context,
G_FLAG );
Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Payment Forecast History Insert '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
Output_Debug (l_full_path, ' IGCCMPSB -- ************ Insert Payment Forecast History Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_DET_PF_HST_INSERT');
SELECT rowid
INTO l_pf_row_id
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = l_cc_pf_target.cc_det_pf_line_id;
IGC_CC_DET_PF_PKG.Update_Row(
l_api_version,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_pf_row_id,
l_cc_pf_target.CC_Det_PF_Line_Id,
l_cc_pf_target.CC_Det_PF_Line_Num,
l_cc_pf_target.CC_Acct_Line_Id,
l_cc_pf_target.Parent_Acct_Line_Id,
l_cc_pf_target.Parent_Det_PF_Line_Id,
l_cc_pf_target.CC_Det_PF_Entered_Amt,
l_cc_pf_target.cc_det_pf_func_amt,
l_cc_pf_target.cc_det_pf_date,
l_cc_pf_target.CC_Det_PF_Billed_Amt,
l_cc_pf_target.CC_Det_PF_Unbilled_Amt,
l_cc_pf_target.cc_det_pf_encmbrnc_amt,
l_cc_pf_target.cc_det_pf_encmbrnc_date,
l_cc_pf_target.CC_Det_PF_Encmbrnc_Status,
l_cc_pf_target.Last_Update_Date,
l_cc_pf_target.Last_Updated_By,
l_cc_pf_target.Last_Update_Login,
l_cc_pf_target.Creation_Date,
l_cc_pf_target.Created_By,
l_cc_pf_target.Attribute1,
l_cc_pf_target.Attribute2,
l_cc_pf_target.Attribute3,
l_cc_pf_target.Attribute4,
l_cc_pf_target.Attribute5,
l_cc_pf_target.Attribute6,
l_cc_pf_target.Attribute7,
l_cc_pf_target.Attribute8,
l_cc_pf_target.Attribute9,
l_cc_pf_target.Attribute10,
l_cc_pf_target.Attribute11,
l_cc_pf_target.Attribute12,
l_cc_pf_target.Attribute13,
l_cc_pf_target.Attribute14,
l_cc_pf_target.Attribute15,
l_cc_pf_target.Context,
G_FLAG );
Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Payment Forecast Update '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
Output_Debug (l_full_path, ' IGCCMPSB -- ************ Update Payment Forecast Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_DET_PF_UPDATE');
/* Update PF Line History */
l_return_status := FND_API.G_RET_STS_SUCCESS;
IGC_CC_DET_PF_HISTORY_PKG.Insert_Row(
l_api_version,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_hist_pf_row_id,
l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id,
l_cc_pmt_fcst_rec.CC_Det_PF_Line_Num,
l_cc_pmt_fcst_rec.CC_Acct_Line_Id,
l_cc_pmt_fcst_rec.Parent_Acct_Line_Id,
l_cc_pmt_fcst_rec.Parent_Det_PF_Line_Id,
l_cc_headers_rec.cc_version_num,
'U',
l_cc_pmt_fcst_rec.CC_Det_PF_Entered_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Func_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Date,
l_cc_pmt_fcst_rec.CC_Det_PF_Billed_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Unbilled_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Date,
l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Status,
l_cc_pmt_fcst_rec.Last_Update_Date,
l_cc_pmt_fcst_rec.Last_Updated_By,
l_cc_pmt_fcst_rec.Last_Update_Login,
l_cc_pmt_fcst_rec.Creation_Date,
l_cc_pmt_fcst_rec.Created_By,
l_cc_pmt_fcst_rec.Attribute1,
l_cc_pmt_fcst_rec.Attribute2,
l_cc_pmt_fcst_rec.Attribute3,
l_cc_pmt_fcst_rec.Attribute4,
l_cc_pmt_fcst_rec.Attribute5,
l_cc_pmt_fcst_rec.Attribute6,
l_cc_pmt_fcst_rec.Attribute7,
l_cc_pmt_fcst_rec.Attribute8,
l_cc_pmt_fcst_rec.Attribute9,
l_cc_pmt_fcst_rec.Attribute10,
l_cc_pmt_fcst_rec.Attribute11,
l_cc_pmt_fcst_rec.Attribute12,
l_cc_pmt_fcst_rec.Attribute13,
l_cc_pmt_fcst_rec.Attribute14,
l_cc_pmt_fcst_rec.Attribute15,
l_cc_pmt_fcst_rec.Context,
G_FLAG );
Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Insert Payment Forecast history '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
Output_Debug (l_full_path, ' IGCCMPSB -- ************ Insert Payment Forecast history Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_DET_PF_HST_INSERT');
SELECT rowid
INTO l_pf_row_id
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = l_cc_pmt_fcst_rec.cc_det_pf_line_id;
IGC_CC_DET_PF_PKG.Update_Row(
l_api_version,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_pf_row_id,
l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id,
l_cc_pmt_fcst_rec.CC_Det_PF_Line_Num,
l_cc_pmt_fcst_rec.CC_Acct_Line_Id,
l_cc_pmt_fcst_rec.Parent_Acct_Line_Id,
l_cc_pmt_fcst_rec.Parent_Det_PF_Line_Id,
l_cc_pmt_fcst_rec.CC_Det_PF_Entered_Amt,
l_cc_pmt_fcst_rec.cc_det_pf_func_amt,
l_cc_pmt_fcst_rec.cc_det_pf_date,
l_cc_pmt_fcst_rec.CC_Det_PF_Billed_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Unbilled_Amt,
l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_amt,
l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_date,
l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Status,
l_cc_pmt_fcst_rec.Last_Update_Date,
l_cc_pmt_fcst_rec.Last_Updated_By,
l_cc_pmt_fcst_rec.Last_Update_Login,
l_cc_pmt_fcst_rec.Creation_Date,
l_cc_pmt_fcst_rec.Created_By,
l_cc_pmt_fcst_rec.Attribute1,
l_cc_pmt_fcst_rec.Attribute2,
l_cc_pmt_fcst_rec.Attribute3,
l_cc_pmt_fcst_rec.Attribute4,
l_cc_pmt_fcst_rec.Attribute5,
l_cc_pmt_fcst_rec.Attribute6,
l_cc_pmt_fcst_rec.Attribute7,
l_cc_pmt_fcst_rec.Attribute8,
l_cc_pmt_fcst_rec.Attribute9,
l_cc_pmt_fcst_rec.Attribute10,
l_cc_pmt_fcst_rec.Attribute11,
l_cc_pmt_fcst_rec.Attribute12,
l_cc_pmt_fcst_rec.Attribute13,
l_cc_pmt_fcst_rec.Attribute14,
l_cc_pmt_fcst_rec.Attribute15,
l_cc_pmt_fcst_rec.Context,
G_FLAG );
Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Update Payment Forecast '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
Output_Debug (l_full_path, ' IGCCMPSB -- ************ Update Payment Forecast Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_DET_PF_UPDATE');
SELECT rowid
INTO l_hdr_row_id
FROM igc_cc_headers
WHERE CC_HEADER_ID = l_cc_headers_rec.cc_header_id;
IGC_CC_HEADERS_PKG.Update_Row(
l_api_version,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_hdr_row_id,
l_cc_headers_rec.CC_HEADER_ID,
l_cc_headers_rec.ORG_ID,
l_cc_headers_rec.CC_TYPE,
l_cc_headers_rec.CC_NUM,
l_cc_version_num,
l_cc_headers_rec.PARENT_HEADER_ID,
l_cc_headers_rec.CC_STATE,
l_cc_headers_rec.CC_CTRL_STATUS,
l_cc_headers_rec.CC_ENCMBRNC_STATUS,
l_cc_apprvl_status,
l_cc_headers_rec.VENDOR_ID,
l_cc_headers_rec.VENDOR_SITE_ID,
l_cc_headers_rec.VENDOR_CONTACT_ID,
l_cc_headers_rec.TERM_ID,
l_cc_headers_rec.LOCATION_ID,
l_cc_headers_rec.SET_OF_BOOKS_ID,
l_cc_headers_rec.CC_ACCT_DATE,
l_cc_headers_rec.CC_DESC,
l_cc_headers_rec.CC_START_DATE,
l_cc_headers_rec.CC_END_DATE,
l_cc_headers_rec.CC_OWNER_USER_ID,
l_cc_headers_rec.CC_PREPARER_USER_ID,
l_cc_headers_rec.CURRENCY_CODE,
l_cc_headers_rec.CONVERSION_TYPE,
l_cc_headers_rec.CONVERSION_DATE,
l_cc_headers_rec.CONVERSION_RATE,
l_cc_headers_rec.LAST_UPDATE_DATE,
l_cc_headers_rec.LAST_UPDATED_BY,
l_cc_headers_rec.LAST_UPDATE_LOGIN,
l_cc_headers_rec.CREATED_BY,
l_cc_headers_rec.CREATION_DATE,
l_cc_headers_rec.CC_CURRENT_USER_ID,
l_cc_headers_rec.WF_ITEM_TYPE,
l_cc_headers_rec.WF_ITEM_KEY,
l_cc_headers_rec.ATTRIBUTE1,
l_cc_headers_rec.ATTRIBUTE2,
l_cc_headers_rec.ATTRIBUTE3,
l_cc_headers_rec.ATTRIBUTE4,
l_cc_headers_rec.ATTRIBUTE5,
l_cc_headers_rec.ATTRIBUTE6,
l_cc_headers_rec.ATTRIBUTE7,
l_cc_headers_rec.ATTRIBUTE8,
l_cc_headers_rec.ATTRIBUTE9,
l_cc_headers_rec.ATTRIBUTE10,
l_cc_headers_rec.ATTRIBUTE11,
l_cc_headers_rec.ATTRIBUTE12,
l_cc_headers_rec.ATTRIBUTE13,
l_cc_headers_rec.ATTRIBUTE14,
l_cc_headers_rec.ATTRIBUTE15,
l_cc_headers_rec.CONTEXT,
l_cc_headers_rec.CC_GUARANTEE_FLAG, -- 2043221
G_FLAG);
Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Header Update'|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Header Update Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_HEADERS_UPDATE');
/* Update Corresponding PO */
-- Fixed the Bug 1632315
IF l_approval_status = 'AP' THEN
BEGIN
-- Performance Tuning. Replaced the select with
-- the one below
-- SELECT 'Y'
-- INTO l_DUMMY
-- FROM po_headers pha1
-- WHERE pha1.po_header_id = (SELECT pha2.po_header_id
-- FROM igc_cc_headers cchd,
-- po_headers pha2
-- WHERE cchd.org_id = p_org_id
-- AND cchd.cc_header_id = l_cc_headers_rec.cc_header_id
-- AND cchd.cc_num = pha2.segment1
-- AND pha2.type_lookup_code = 'STANDARD');
SELECT 'Y'
INTO l_dummy
FROM po_headers_all pha1,
igc_cc_headers cchd
WHERE cchd.org_id = p_org_id
AND cchd.cc_header_id = l_cc_headers_rec.cc_header_id
AND cchd.cc_num = pha1.segment1
AND pha1.type_lookup_code = 'STANDARD'
AND pha1.org_id = p_org_id;
Output_Debug (l_full_path, ' IGCCMPSB -- ************ Before Update PO '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Update PO '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Update PO Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_PO_UPDATE_FAILED');
IGC_CC_ACTIONS_PKG.Insert_Row(
l_api_version,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_action_row_id,
l_cc_headers_rec.CC_HEADER_ID,
l_cc_version_num,
'MS',
l_cc_headers_rec.CC_STATE,
l_cc_headers_rec.CC_CTRL_STATUS,
l_cc_apprvl_status,
l_action_hist_msg,
Sysdate,
l_Last_Updated_By,
l_Last_Update_Login,
Sysdate,
l_Created_By);
Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Insert Action History '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Insert Action History Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_ACTION_HST_INSERT');
/* Update validation status, in temporary table*/
UPDATE igc_cc_process_data
SET
processed = 'Y'
WHERE request_id = p_request_id AND
cc_header_id = P_cc_header_id ;
FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_CC_UPDATE_FAILED');
END MPFS_UPDATE;
SELECT cchd.cc_header_id, cchd.cc_apprvl_status,
SUM
( ccdpf.cc_det_pf_func_amt
- igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
(ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
ccdpf.cc_acct_line_id
)
) tot_unbilled_amt
FROM igc_cc_headers cchd,
igc_cc_acct_lines ccal,
igc_cc_det_pf ccdpf
WHERE cchd.cc_header_id = ccal.cc_header_id
AND ccal.cc_acct_line_id = ccdpf.cc_acct_line_id
AND cchd.org_id = p_org_id
AND cchd.set_of_books_id = p_set_of_books_id
AND cchd.cc_type = 'S'
AND cchd.cc_state = 'CM'
AND cchd.cc_encmbrnc_status = 'C'
AND cchd.cc_owner_user_id = p_owner_id
AND ccdpf.cc_det_pf_date >= p_start_date
AND ccdpf.cc_det_pf_date <= p_end_date
AND ( NVL (ccdpf.cc_det_pf_func_amt, 0)
- NVL
(igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
(ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
ccdpf.cc_acct_line_id
),
0
)
) > 0
GROUP BY cchd.cc_header_id, cchd.cc_apprvl_status;
SELECT cchd.cc_header_id, cchd.cc_apprvl_status,
SUM
( ccdpf.cc_det_pf_func_amt
- igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
(ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
ccdpf.cc_acct_line_id
)
) tot_unbilled_amt
FROM igc_cc_headers cchd,
igc_cc_acct_lines ccal,
igc_cc_det_pf ccdpf
WHERE cchd.cc_header_id = ccal.cc_header_id
AND ccal.cc_acct_line_id = ccdpf.cc_acct_line_id
AND cchd.org_id = p_org_id
AND cchd.set_of_books_id = p_set_of_books_id
AND cchd.cc_type = 'S'
AND cchd.cc_state = 'CM'
AND cchd.cc_encmbrnc_status = 'C'
AND ccdpf.cc_det_pf_date >= p_start_date
AND ccdpf.cc_det_pf_date <= p_end_date
AND ( NVL (ccdpf.cc_det_pf_func_amt, 0)
- NVL
(igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
(ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
ccdpf.cc_acct_line_id
),
0
)
) > 0
GROUP BY cchd.cc_header_id, cchd.cc_apprvl_status;
SELECT *
FROM igc_cc_process_data ccpd
WHERE ccpd.set_of_books_id = l_sob_id
AND ccpd.request_id = l_request_id1
AND ccpd.org_id = l_org_id
AND ccpd.process_type = l_process_type
AND (ccpd.processed <> 'Y' OR ccpd.processed IS NULL);
SELECT cchd.cc_header_id, cchd.cc_num, ccdpf.cc_acct_line_id,
ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_date
FROM igc_cc_headers cchd,
igc_cc_acct_lines ccal,
igc_cc_det_pf ccdpf,
igc_cc_process_data ccpd
WHERE cchd.cc_header_id = ccpd.cc_header_id
AND cchd.cc_header_id = ccal.cc_header_id
AND ccal.cc_acct_line_id = ccdpf.cc_acct_line_id
AND cchd.org_id = l_org_id
AND cchd.set_of_books_id = l_sob_id
AND ccpd.request_id = p_request_id1
AND ccpd.process_type = p_process_type
AND (ccpd.processed <> 'Y' OR ccpd.processed IS NULL)
AND ccdpf.cc_det_pf_date >= p_start_date
AND ccdpf.cc_det_pf_date <= p_end_date
AND ( ccdpf.cc_det_pf_func_amt
- igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
(ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
ccdpf.cc_acct_line_id
)
) > 0;
SELECT cchd.cc_header_id, cchd.cc_num
FROM igc_cc_headers cchd,
igc_cc_process_data ccpd
WHERE cchd.cc_header_id = ccpd.cc_header_id
AND cchd.org_id = l_org_id
AND cchd.set_of_books_id = l_sob_id
AND ccpd.request_id = p_request_id1
AND ccpd.process_type = p_process_type
AND (ccpd.processed <> 'Y' OR ccpd.processed IS NULL);
SELECT ccdpf.cc_acct_line_id, ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_date
FROM igc_cc_acct_lines ccal,
igc_cc_det_pf ccdpf
WHERE ccal.cc_header_id = p_cc_header_id
AND ccal.cc_acct_line_id = ccdpf.cc_acct_line_id
AND ccdpf.cc_det_pf_date >= p_start_date
AND ccdpf.cc_det_pf_date <= p_end_date
AND ( ccdpf.cc_det_pf_func_amt
- igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
(ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
ccdpf.cc_acct_line_id
)
) > 0;
SELECT *
FROM igc_cc_process_data ccpd
WHERE ccpd.request_id = l_request_id1
AND ccpd.org_id = l_org_id
AND ccpd.process_type = l_process_type
AND ccpd.validation_status = 'P'
AND (ccpd.processed <> 'Y' OR ccpd.processed IS NULL);
SELECT ccdpf.ROWID, ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num,
NULL cc_acct_line_num, ccdpf.cc_acct_line_id,
NULL parent_det_pf_line_num, ccdpf.parent_det_pf_line_id,
ccdpf.parent_acct_line_id, ccdpf.cc_det_pf_entered_amt,
ccdpf.cc_det_pf_func_amt, ccdpf.cc_det_pf_date,
igc_cc_comp_amt_pkg.compute_pf_billed_amt
(ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
ccdpf.cc_acct_line_id
) cc_det_pf_billed_amt,
igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
(ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
ccdpf.cc_acct_line_id
) cc_det_pf_func_billed_amt,
ccdpf.cc_det_pf_unbilled_amt,
igc_cc_comp_amt_pkg.compute_functional_amt
(p_cc_header_id,
NVL (ccdpf.cc_det_pf_entered_amt, 0)
) cc_det_pf_comp_func_amt,
ccdpf.cc_det_pf_encmbrnc_amt,
( igc_cc_comp_amt_pkg.compute_functional_amt
(p_cc_header_id,
NVL (ccdpf.cc_det_pf_entered_amt,
0
)
)
- NVL (ccdpf.cc_det_pf_encmbrnc_amt, 0)
) cc_det_pf_unencmbrd_amt,
ccdpf.cc_det_pf_encmbrnc_date, ccdpf.cc_det_pf_encmbrnc_status,
ccdpf.CONTEXT, ccdpf.attribute1, ccdpf.attribute2,
ccdpf.attribute3, ccdpf.attribute4, ccdpf.attribute5,
ccdpf.attribute6, ccdpf.attribute7, ccdpf.attribute8,
ccdpf.attribute9, ccdpf.attribute10, ccdpf.attribute11,
ccdpf.attribute12, ccdpf.attribute13, ccdpf.attribute14,
ccdpf.attribute15, ccdpf.last_update_date, ccdpf.last_updated_by,
ccdpf.last_update_login, ccdpf.creation_date, ccdpf.created_by
FROM igc_cc_det_pf ccdpf
WHERE ccdpf.cc_acct_line_id = p_cc_acct_line_id
AND ccdpf.cc_det_pf_date >= p_target_date
AND ccdpf.cc_det_pf_line_num =
(SELECT MIN (ccdpf1.cc_det_pf_line_num)
FROM igc_cc_det_pf ccdpf1
WHERE ccdpf1.cc_acct_line_id = p_cc_acct_line_id
AND ccdpf1.cc_det_pf_date = /* bug fix 1702768 */
(SELECT MIN (ccdpf2.cc_det_pf_date)
FROM igc_cc_det_pf ccdpf2
WHERE ccdpf2.cc_acct_line_id = p_cc_acct_line_id
AND ccdpf2.cc_det_pf_date >= p_target_date));
l_result_mpfs_update VARCHAR2 (1);
l_result_mpfs_update := 'F';
SELECT meaning
INTO l_option_name
FROM igi_lookups
WHERE lookup_code = 'CC' AND lookup_type = 'GCC_DESCRIPTION';
DELETE FROM igc_cc_interface
WHERE TO_DATE (creation_date, 'DD/MM/YYYY') <=
TO_DATE (SYSDATE, 'DD/MM/YYYY')
- 2;
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase, cc_header_id,
cc_acct_line_id, cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase, NULL,
NULL, NULL, l_usr_msg,
l_org_id, l_sob_id, l_request_id1
);
/* Deletes from IGC_CC_PROCESS_DATA with PHASE - Preliminary and TYPE - MPFS */
DELETE FROM igc_cc_process_data a
WHERE a.process_type = l_process_type
AND a.process_phase = 'P'
AND a.org_id = l_org_id
AND a.set_of_books_id = l_sob_id;
/* Deletes from IGC_CC_PROCESS_EXCEPTIONS with Type - MPFS */
DELETE FROM igc_cc_process_exceptions b
WHERE b.process_type = l_process_type
AND b.org_id = l_org_id
AND b.set_of_books_id = l_sob_id;
/* Deletes Unprocessed lines for Final Phase */
DELETE FROM igc_cc_process_data a
WHERE a.process_type = l_process_type
AND a.process_phase IN ('F', 'P')
AND (a.processed <> 'Y' OR a.processed IS NULL)
AND a.org_id = l_org_id
AND a.set_of_books_id = l_sob_id;
/* Updates with new Request ID to those which were processed */
/*
UPDATE IGC_CC_PROCESS_DATA A
SET REQUEST_ID = l_request_id1
WHERE A.PROCESS_TYPE = l_process_type
AND A.PROCESS_PHASE = 'F'
AND A.PROCESSED = 'Y'
AND A.ORG_ID = l_org_id
AND A.SET_OF_BOOKS_ID = l_sob_id;
/* Select the contracts based on the parameters */
/* Selection and Filteration Phase Starts */
l_exception := NULL;
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase, cc_header_id,
cc_acct_line_id, cc_det_pf_line_id,
exception_reason, org_id, set_of_books_id,
request_id
)
VALUES (l_process_type, p_process_phase, NULL,
NULL, NULL,
l_exception, l_org_id, l_sob_id,
l_request_id1
);
SELECT cc_header_id
INTO l_header_id
FROM igc_cc_process_data ccpd
WHERE ccpd.cc_header_id = v1.cc_header_id
AND ccpd.request_id = l_request_id1;
UPDATE igc_cc_process_data ccpd
SET old_approval_status = v1.cc_apprvl_status
WHERE ccpd.cc_header_id = v1.cc_header_id
AND ccpd.org_id = l_org_id
AND ccpd.set_of_books_id = l_sob_id;
INSERT INTO igc_cc_process_data
(process_type, process_phase,
cc_header_id, validation_status,
reservation_status, processed,
old_approval_status, org_id,
set_of_books_id, validate_only, request_id
)
VALUES (l_process_type, p_process_phase,
v1.cc_header_id, 'I',
'F', 'N',
v1.cc_apprvl_status, l_org_id,
l_sob_id, NULL, l_request_id1
);
INSERT INTO igc_cc_process_data
(process_type, process_phase,
cc_header_id, validation_status,
reservation_status, processed,
old_approval_status, org_id, set_of_books_id,
validate_only, request_id
)
VALUES (l_process_type, p_process_phase,
v1.cc_header_id, 'I',
'F', 'N',
v1.cc_apprvl_status, l_org_id, l_sob_id,
NULL, l_request_id1
);
' IGCCMPSB -- ************ Zero Rows Selected '
|| TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
|| ' *************************'
);
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase, cc_header_id,
cc_acct_line_id, cc_det_pf_line_id,
exception_reason, org_id, set_of_books_id,
request_id
)
VALUES (l_process_type, p_process_phase, NULL,
NULL, NULL,
l_exception, l_org_id, l_sob_id,
l_request_id1
);
SELECT cc_header_id
INTO l_header_id
FROM igc_cc_process_data ccpd
WHERE ccpd.cc_header_id = v2.cc_header_id
AND ccpd.request_id = l_request_id1;
UPDATE igc_cc_process_data ccpd
SET old_approval_status = v2.cc_apprvl_status
WHERE ccpd.cc_header_id = v2.cc_header_id
AND ccpd.org_id = l_org_id
AND ccpd.set_of_books_id = l_sob_id;
' IGCCMPSB -- ************ Insert data into igc_cc_process_data '
|| TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
|| ' *************************'
);
INSERT INTO igc_cc_process_data
(process_type, process_phase,
cc_header_id, validation_status,
reservation_status, processed,
old_approval_status, org_id,
set_of_books_id, validate_only, request_id
)
VALUES (l_process_type, p_process_phase,
v2.cc_header_id, 'I',
'F', 'N',
v2.cc_apprvl_status, l_org_id,
l_sob_id, NULL, l_request_id1
);
INSERT INTO igc_cc_process_data
(process_type, process_phase,
cc_header_id, validation_status,
reservation_status, processed,
old_approval_status, org_id, set_of_books_id,
validate_only, request_id
)
VALUES (l_process_type, p_process_phase,
v2.cc_header_id, 'I',
'F', 'N',
v2.cc_apprvl_status, l_org_id, l_sob_id,
NULL, l_request_id1
);
/* End of Contract Selection and Filteration */
-- Flag Mark 2
SELECT COUNT (ROWID)
INTO l_rec_found
FROM igc_cc_process_data
WHERE request_id = l_request_id1
AND set_of_books_id = l_sob_id
AND org_id = l_org_id
AND process_type = l_process_type;
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase, cc_header_id,
cc_acct_line_id, cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase, NULL,
NULL, NULL, l_exception,
l_org_id, l_sob_id, l_request_id1
);
SELECT cc_apprvl_status
INTO l_previous_apprvl_status
FROM igc_cc_headers cchd
WHERE cchd.cc_header_id = v4.cc_header_id;
SELECT 'X'
INTO l_dummy
FROM igc_cc_process_exceptions
WHERE cc_header_id = v4.cc_header_id
AND cc_acct_line_id IS NULL
AND cc_det_pf_line_id IS NULL
AND org_id = l_org_id
AND process_type = l_process_type
AND process_phase = p_process_phase
AND set_of_books_id = l_sob_id
AND request_id = l_request_id1;
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4.cc_header_id, NULL,
NULL, l_exception,
l_org_id, l_sob_id, l_request_id1
);
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason, org_id,
set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4.cc_header_id, v4.cc_acct_line_id,
v4.cc_det_pf_line_id, l_exception, l_org_id,
l_sob_id, l_request_id1
);
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4.cc_header_id, v4.cc_acct_line_id,
v4.cc_det_pf_line_id, l_exception,
l_org_id, l_sob_id, l_request_id1
);
SELECT 'X'
INTO l_dummy
FROM igc_cc_process_exceptions
WHERE cc_header_id = v4.cc_header_id
AND cc_acct_line_id = v4.cc_acct_line_id
AND cc_det_pf_line_id IS NULL
AND org_id = l_org_id
AND process_type = l_process_type
AND process_phase = p_process_phase
AND set_of_books_id = l_sob_id
AND request_id = l_request_id1;
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4.cc_header_id, v4.cc_acct_line_id,
NULL, l_exception,
l_org_id, l_sob_id, l_request_id1
);
SELECT 'X'
INTO l_dummy
FROM igc_cc_process_exceptions
WHERE cc_header_id = v4.cc_header_id
AND cc_acct_line_id = v4.cc_acct_line_id
AND cc_det_pf_line_id = v7.cc_det_pf_line_id
AND org_id = l_org_id
AND process_type = l_process_type
AND process_phase = p_process_phase
AND set_of_books_id = l_sob_id
AND request_id = l_request_id1;
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4.cc_header_id, v4.cc_acct_line_id,
v7.cc_det_pf_line_id, l_exception,
l_org_id, l_sob_id, l_request_id1
);
SELECT 'X'
INTO l_dummy
FROM igc_cc_process_exceptions
WHERE cc_header_id = v4.cc_header_id
AND cc_acct_line_id = v4.cc_acct_line_id
AND cc_det_pf_line_id = v7.cc_det_pf_line_id
AND org_id = l_org_id
AND process_type = l_process_type
AND process_phase = p_process_phase
AND set_of_books_id = l_sob_id
AND request_id = l_request_id1;
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4.cc_header_id, v4.cc_acct_line_id,
v7.cc_det_pf_line_id, l_exception,
l_org_id, l_sob_id, l_request_id1
);
SELECT 'X'
INTO l_DUMMY
FROM igc_cc_process_exceptions
WHERE cc_header_id = V4.cc_header_id
AND cc_acct_line_id IS NULL
AND org_id = l_org_id
AND process_type = l_process_type
AND process_phase = p_process_phase
AND set_of_books_id = l_sob_id
AND request_id = l_request_id1;
INSERT INTO igc_cc_process_exceptions
(PROCESS_TYPE ,
PROCESS_PHASE ,
CC_HEADER_ID ,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID ,
EXCEPTION_REASON ,
ORG_ID ,
SET_OF_BOOKS_ID ,
REQUEST_ID )
values (l_process_type,
p_process_phase,
V4.cc_header_id,
NULL,
NULL,
l_EXCEPTION,
l_org_id,
l_sob_id,
l_request_id1);
UPDATE igc_cc_process_data ccpd
SET ccpd.validation_status = 'P'
WHERE ccpd.cc_header_id = v4.cc_header_id
AND ccpd.org_id = l_org_id
AND ccpd.set_of_books_id = l_sob_id
AND ccpd.request_id = l_request_id1
AND ccpd.process_type = l_process_type;
UPDATE igc_cc_process_data ccpd
SET ccpd.validation_status = 'F'
WHERE ccpd.cc_header_id = v4.cc_header_id
AND ccpd.org_id = l_org_id
AND ccpd.set_of_books_id = l_sob_id
AND ccpd.request_id = l_request_id1
AND ccpd.process_type = l_process_type;
SELECT cc_num
INTO l_cc_num
FROM igc_cc_headers
WHERE cc_header_id = v4_1.cc_header_id;
' IGCCMPSB -- ************ Before Insert lock exception '
|| TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
|| ' *************************'
);
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase, cc_header_id,
cc_acct_line_id, cc_det_pf_line_id,
exception_reason, org_id, set_of_books_id,
request_id
)
VALUES (l_process_type, p_process_phase, v4_1.cc_header_id,
NULL, NULL,
l_exception, l_org_id, l_sob_id,
l_request_id1
);
' IGCCMPSB -- ************ After Insert lock exception '
|| TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
|| ' *************************'
);
' IGCCMPSB -- ************ Before Insert PO Lock Exception '
|| TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
|| ' *************************'
);
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase, cc_header_id,
cc_acct_line_id, cc_det_pf_line_id,
exception_reason, org_id, set_of_books_id,
request_id
)
VALUES (l_process_type, p_process_phase, v4_1.cc_header_id,
NULL, NULL,
l_exception, l_org_id, l_sob_id,
l_request_id1
);
' IGCCMPSB -- ************ After Insert PO Lock Exception '
|| TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
|| ' *************************'
);
' IGCCMPSB -- ************ Update IGC_Process_Data After successful Lock'
|| TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
|| ' *************************'
);
SELECT cc_apprvl_status
INTO l_previous_apprvl_status
FROM igc_cc_headers cchd
WHERE cchd.cc_header_id = v4_1.cc_header_id;
SELECT 'X'
INTO l_dummy
FROM igc_cc_process_exceptions
WHERE cc_header_id = v4_1.cc_header_id
AND cc_acct_line_id IS NULL
AND cc_det_pf_line_id IS NULL
AND org_id = l_org_id
AND process_type = l_process_type
AND process_phase = p_process_phase
AND set_of_books_id = l_sob_id
AND request_id = l_request_id1;
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4_1.cc_header_id, NULL,
NULL, l_exception,
l_org_id, l_sob_id, l_request_id1
);
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4.cc_header_id, v4.cc_acct_line_id,
v4.cc_det_pf_line_id, l_exception,
l_org_id, l_sob_id, l_request_id1
);
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4_1.cc_header_id, v4_2.cc_acct_line_id,
v4_2.cc_det_pf_line_id, l_exception,
l_org_id, l_sob_id, l_request_id1
);
SELECT 'X'
INTO l_dummy
FROM igc_cc_process_exceptions
WHERE cc_header_id = v4_1.cc_header_id
AND cc_acct_line_id = v4_2.cc_acct_line_id
AND cc_det_pf_line_id IS NULL
AND org_id = l_org_id
AND process_type = l_process_type
AND process_phase = p_process_phase
AND set_of_books_id = l_sob_id
AND request_id = l_request_id1;
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4_1.cc_header_id, v4_2.cc_acct_line_id,
NULL, l_exception,
l_org_id, l_sob_id, l_request_id1
);
SELECT 'X'
INTO l_dummy
FROM igc_cc_process_exceptions
WHERE cc_header_id = v4_1.cc_header_id
AND cc_acct_line_id = v4_2.cc_acct_line_id
AND cc_det_pf_line_id = v7.cc_det_pf_line_id
AND org_id = l_org_id
AND process_type = l_process_type
AND process_phase = p_process_phase
AND set_of_books_id = l_sob_id
AND request_id = l_request_id1;
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4_1.cc_header_id, v4_2.cc_acct_line_id,
v7.cc_det_pf_line_id, l_exception,
l_org_id, l_sob_id, l_request_id1
);
SELECT 'X'
INTO l_dummy
FROM igc_cc_process_exceptions
WHERE cc_header_id = v4_1.cc_header_id
AND cc_acct_line_id = v4_2.cc_acct_line_id
AND cc_det_pf_line_id = v7.cc_det_pf_line_id
AND org_id = l_org_id
AND process_type = l_process_type
AND process_phase = p_process_phase
AND set_of_books_id = l_sob_id
AND request_id = l_request_id1;
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v4_1.cc_header_id, v4_2.cc_acct_line_id,
v7.cc_det_pf_line_id, l_exception,
l_org_id, l_sob_id, l_request_id1
);
SELECT 'X'
INTO l_DUMMY
FROM igc_cc_process_exceptions
WHERE cc_header_id = V4.cc_header_id
AND cc_acct_line_id IS NULL
AND org_id = l_org_id
AND process_type = l_process_type
AND process_phase = p_process_phase
AND set_of_books_id = l_sob_id
AND request_id = l_request_id1;
INSERT INTO igc_cc_process_exceptions
(PROCESS_TYPE ,
PROCESS_PHASE ,
CC_HEADER_ID ,
CC_ACCT_LINE_ID ,
CC_DET_PF_LINE_ID ,
EXCEPTION_REASON ,
ORG_ID ,
SET_OF_BOOKS_ID ,
REQUEST_ID )
values (l_process_type,
p_process_phase,
V4.cc_header_id,
NULL,
NULL,
l_EXCEPTION,
l_org_id,
l_sob_id,
l_request_id1);
UPDATE igc_cc_process_data ccpd
SET ccpd.validation_status = 'P'
WHERE ccpd.cc_header_id = v4_1.cc_header_id
AND ccpd.org_id = l_org_id
AND ccpd.set_of_books_id = l_sob_id
AND ccpd.request_id = l_request_id1
AND ccpd.process_type = l_process_type;
UPDATE igc_cc_headers cchd
SET cchd.cc_apprvl_status = 'IP'
WHERE cchd.cc_header_id = v4.cc_header_id;
-- Replaced the following select with the one below
-- SELECT 'Y'
-- INTO l_DUMMY
-- FROM po_headers pha1
-- WHERE pha1.po_header_id = (SELECT pha2.po_header_id
-- FROM igc_cc_headers cchd,
-- po_headers pha2
-- WHERE cchd.org_id = l_org_id
-- AND cchd.cc_header_id = V4.cc_header_id
-- AND cchd.cc_num = pha2.segment1
-- AND pha2.type_lookup_code = 'STANDARD');
SELECT 'Y'
INTO l_dummy
FROM po_headers_all pha1, igc_cc_headers cchd
WHERE cchd.org_id = l_org_id
AND cchd.cc_header_id = v4_1.cc_header_id
AND cchd.cc_num = pha1.segment1
AND pha1.type_lookup_code = 'STANDARD'
AND pha1.org_id = l_org_id;
-- Replaced the following update with the one below
-- UPDATE po_headers pha1
-- SET pha1.approved_flag = 'N'
-- WHERE (pha1.segment1,pha1.org_id,pha1.type_lookup_code) IN
-- (SELECT pha2.segment1,pha2.org_id,pha2.type_lookup_code
-- FROM po_headers pha2, igc_cc_headers cchd
-- WHERE cchd.cc_header_id = V4.cc_header_id
-- AND pha2.segment1 = cchd.cc_num
-- AND pha2.org_id = cchd.org_id
-- AND pha2.type_lookup_code = 'STANDARD');
UPDATE po_headers_all pha1
SET pha1.approved_flag = 'N'
WHERE pha1.type_lookup_code = 'STANDARD'
AND pha1.org_id = l_org_id
AND pha1.segment1 =
(SELECT cchd.cc_num
FROM igc_cc_headers cchd
WHERE cchd.cc_header_id = v4_1.cc_header_id);
UPDATE igc_cc_process_data ccpd
SET ccpd.validation_status = 'L'
WHERE ccpd.cc_header_id = v4_1.cc_header_id
AND ccpd.org_id = l_org_id
AND ccpd.set_of_books_id = l_sob_id
AND ccpd.request_id = l_request_id1
AND ccpd.process_type = l_process_type;
UPDATE igc_cc_process_data ccpd
SET ccpd.reservation_status = 'P'
WHERE ccpd.cc_header_id = v5.cc_header_id
AND ccpd.request_id = l_request_id1;
/* Call mpfs Update */
IF (g_debug_mode = 'Y')
THEN
output_debug (l_full_path,
' IGCCMPSB -- ************ Before Update '
|| TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
|| ' *************************'
);
l_result_mpfs_update :=
mpfs_update (v5.cc_header_id,
l_request_id1,
l_sob_id,
l_org_id,
l_start_date,
l_end_date,
l_target_date,
l_transfer_date,
l_err_mesg
);
' IGCCMPSB -- ************ After Update '
|| TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
|| ' *************************'
);
IF (l_result_mpfs_update = 'F')
THEN
IF (g_debug_mode = 'Y')
THEN
output_debug
(l_full_path,
' IGCCMPSB -- ************ Update Failure '
|| TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
|| ' *************************'
);
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase,
cc_header_id, cc_acct_line_id,
cc_det_pf_line_id, exception_reason, org_id,
set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase,
v5.cc_header_id, NULL,
NULL, l_err_mesg, l_org_id,
l_sob_id, l_request_id1
);
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase, cc_header_id,
cc_acct_line_id, cc_det_pf_line_id,
exception_reason, org_id, set_of_books_id,
request_id
)
VALUES (l_process_type, p_process_phase, v5.cc_header_id,
NULL, NULL,
l_exception, l_org_id, l_sob_id,
l_request_id1
);
UPDATE igc_cc_process_data ccpd
SET ccpd.reservation_status = 'F'
WHERE ccpd.cc_header_id = v5.cc_header_id
AND ccpd.set_of_books_id = l_sob_id
AND ccpd.request_id = l_request_id1
AND ccpd.org_id = l_org_id
AND ccpd.process_type = l_process_type;
UPDATE igc_cc_headers cchd
SET cchd.cc_apprvl_status = v5.old_approval_status
WHERE cchd.cc_header_id = v5.cc_header_id;
SELECT 'Y'
INTO l_dummy
FROM po_headers_all pha1, igc_cc_headers cchd
WHERE cchd.org_id = l_org_id
AND cchd.cc_header_id = v5.cc_header_id
AND cchd.cc_num = pha1.segment1
AND pha1.type_lookup_code = 'STANDARD'
AND pha1.org_id = l_org_id;
UPDATE po_headers_all pha1
SET pha1.approved_flag = 'Y'
WHERE pha1.type_lookup_code = 'STANDARD'
AND pha1.org_id = l_org_id
AND pha1.segment1 =
(SELECT cchd.cc_num
FROM igc_cc_headers cchd
WHERE cchd.cc_header_id = v5.cc_header_id);
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase, cc_header_id,
cc_acct_line_id, cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase, NULL,
NULL, NULL, l_exception,
l_org_id, l_sob_id, l_request_id1
);
INSERT INTO igc_cc_process_exceptions
(process_type, process_phase, cc_header_id,
cc_acct_line_id, cc_det_pf_line_id, exception_reason,
org_id, set_of_books_id, request_id
)
VALUES (l_process_type, p_process_phase, NULL,
NULL, NULL, l_exception,
l_org_id, l_sob_id, l_request_id1
);