[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure YEAR_END_UPDATE
=================================================================================*/
FUNCTION YEAR_END_UPDATE ( p_CC_HEADER_ID IN NUMBER,
p_YEAR IN NUMBER,
p_SOB_ID IN NUMBER,
p_REQUEST_ID IN NUMBER,
p_yr_start_date IN DATE,
p_yr_end_date IN DATE,
p_sbc_on IN BOOLEAN,
p_cbc_on IN BOOLEAN,
p_prov_enc_on IN BOOLEAN,
p_conf_enc_on IN BOOLEAN)
RETURN VARCHAR2 AS
CURSOR C14 IS
SELECT *
FROM IGC_CC_HEADERS
WHERE IGC_CC_HEADERS.CC_HEADER_ID = p_CC_HEADER_ID;
SELECT *
FROM IGC_CC_ACCT_LINES A
WHERE A.CC_HEADER_ID = p_cc_header_id AND
EXISTS (SELECT 'X'
FROM IGC_CC_DET_PF B
WHERE B.CC_ACCT_LINE_ID = A.CC_ACCT_LINE_ID AND
( B.CC_DET_PF_DATE >= p_yr_start_date AND B.CC_DET_PF_DATE <= p_yr_end_date) );
SELECT *
FROM IGC_CC_DET_PF B
WHERE B.CC_ACCT_LINE_ID = p_cc_acct_line_id;
l_Last_Updated_By NUMBER := FND_GLOBAL.USER_ID;
l_Last_Update_Login NUMBER := FND_GLOBAL.LOGIN_ID;
l_full_path := g_path||'Year_End_Update';--bug 3199488
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL';
SELECT CC_STATE,CC_ENCMBRNC_STATUS
INTO l_CC_STATE,l_CC_ENCMBRNC_STATUS
FROM IGC_CC_HEADERS A
WHERE A.CC_HEADER_ID = p_CC_HEADER_ID;
SELECT min(gp.period_num)
INTO l_min_period_num
FROM gl_period_statuses gps,
gl_periods gp,
gl_sets_of_books gb
WHERE
gb.set_of_books_id = p_SOB_ID AND
gp.period_set_name = gb.period_set_name AND
gp.period_type = gb.accounted_period_type AND
gps.set_of_books_id = gb.set_of_books_id AND
gps.period_name = gp.period_name AND
gps.application_id = l_application_id AND
gp.period_year = p_year+1 AND
gp.adjustment_period_flag = 'N';
SELECT gps.start_date INTO
l_next_yr_start_date
FROM gl_period_statuses gps,
gl_periods gp,
gl_sets_of_books gb
WHERE gb.set_of_books_id = p_SOB_ID AND
gp.period_set_name = gb.period_set_name AND
gp.period_type = gb.accounted_period_type AND
gps.set_of_books_id = gb.set_of_books_id AND
gps.period_name = gp.period_name AND
gps.application_id = l_application_id AND
gp.period_year = p_year+1 AND
gp.period_num = l_min_period_num;
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;
SELECT ROWID INTO l_HEADERS_ROWID
FROM IGC_CC_HEADERS A
WHERE A.CC_HEADER_ID = V14.CC_HEADER_ID;
IGC_CC_HEADER_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_HEADER_HISTORY_ROWID,
V14.CC_HEADER_ID,
V14.ORG_ID,
V14.CC_TYPE,
V14.CC_NUM,
l_CC_VERSION_NUM,
'U',
V14.CC_STATE,
V14.PARENT_HEADER_ID,
V14.CC_CTRL_STATUS,
V14.CC_ENCMBRNC_STATUS,
l_APPROVAL_STATUS,
V14.VENDOR_ID,
V14.VENDOR_SITE_ID,
V14.VENDOR_CONTACT_ID,
V14.TERM_ID,
V14.LOCATION_ID,
V14.SET_OF_BOOKS_ID,
V14.CC_ACCT_DATE,
V14.CC_DESC,
V14.CC_START_DATE,
V14.CC_END_DATE,
V14.CC_OWNER_USER_ID,
V14.CC_PREPARER_USER_ID,
V14.CURRENCY_CODE,
V14.CONVERSION_TYPE,
V14.CONVERSION_DATE,
V14.CONVERSION_RATE,
V14.LAST_UPDATE_DATE,
V14.LAST_UPDATED_BY,
V14.LAST_UPDATE_LOGIN,
V14.CREATED_BY,
V14.CREATION_DATE,
V14.WF_ITEM_TYPE,
V14.WF_ITEM_KEY,
V14.CC_CURRENT_USER_ID,
V14.ATTRIBUTE1,
V14.ATTRIBUTE2,
V14.ATTRIBUTE3,
V14.ATTRIBUTE4,
V14.ATTRIBUTE5,
V14.ATTRIBUTE6,
V14.ATTRIBUTE7,
V14.ATTRIBUTE8,
V14.ATTRIBUTE9,
V14.ATTRIBUTE10,
V14.ATTRIBUTE11,
V14.ATTRIBUTE12,
V14.ATTRIBUTE13,
V14.ATTRIBUTE14,
V14.ATTRIBUTE15,
V14.CONTEXT,
V14.CC_GUARANTEE_FLAG,
G_FLAG);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_HEADER_HST_INSERT');
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(
'Y',
'F',
V14.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V14.ORG_ID,
p_SOB_ID,
p_REQUEST_ID);
/* Update Account Line History*/
l_return_status := FND_API.G_RET_STS_SUCCESS;
IGC_CC_ACCT_LINE_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_acct_history_rowid,
V15.CC_Acct_Line_Id,
V15.CC_Header_Id,
V15.Parent_Header_Id,
V15.Parent_Acct_Line_Id ,
V15.CC_Acct_Line_Num,
l_CC_VERSION_NUM,
'U',
V15.CC_Charge_Code_Combination_Id,
V15.CC_Budget_Code_Combination_Id,
V15.CC_Acct_Entered_Amt ,
V15.CC_Acct_Func_Amt,
V15.CC_Acct_Desc ,
V15.CC_Acct_Billed_Amt ,
V15.CC_Acct_Unbilled_Amt,
V15.CC_Acct_Taxable_Flag,
Null,-- tax_id Bug 6472296 EB Tax uptake
V15.CC_Acct_Encmbrnc_Amt,
V15.CC_Acct_Encmbrnc_Date,
V15.CC_Acct_Encmbrnc_Status,
V15.Project_Id,
V15.Task_Id,
V15.Expenditure_Type,
V15.Expenditure_Org_Id,
V15.Expenditure_Item_Date,
V15.Last_Update_Date,
V15.Last_Updated_By,
V15.Last_Update_Login ,
V15.Creation_Date ,
V15.Created_By ,
V15.Attribute1,
V15.Attribute2,
V15.Attribute3,
V15.Attribute4,
V15.Attribute5,
V15.Attribute6,
V15.Attribute7,
V15.Attribute8,
V15.Attribute9,
V15.Attribute10,
V15.Attribute11,
V15.Attribute12,
V15.Attribute13,
V15.Attribute14,
V15.Attribute15,
V15.Context,
V15.CC_FUNC_WITHHELD_AMT,
V15.CC_ENT_WITHHELD_AMT,
G_FLAG,
V15.tax_classif_code--Bug 6472296 EB Tax uptake
);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINE_HST_INSERT');
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(
'Y',
'F',
V14.CC_HEADER_ID,
V15.CC_ACCT_LINE_ID,
NULL,
l_EXCEPTION,
V14.ORG_ID,
p_SOB_ID,
p_REQUEST_ID);
SELECT ROWID INTO l_ACCT_ROWID
FROM IGC_CC_ACCT_LINES B
WHERE B.CC_HEADER_ID = V14.CC_HEADER_ID
AND B.CC_ACCT_LINE_ID = V15.CC_ACCT_LINE_ID;
IGC_CC_ACCT_LINES_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_acct_rowid,
V15.CC_Acct_Line_Id,
V15.CC_Header_Id,
V15.Parent_Header_Id,
V15.Parent_Acct_Line_Id ,
V15.CC_Charge_Code_Combination_Id,
V15.CC_Acct_Line_Num,
V15.CC_Budget_Code_Combination_Id,
V15.CC_Acct_Entered_Amt ,
V15.CC_Acct_Func_Amt,
V15.CC_Acct_Desc ,
V15.CC_Acct_Billed_Amt ,
V15.CC_Acct_Unbilled_Amt,
V15.CC_Acct_Taxable_Flag,
Null,--tax_id Bug 6472296 EB Tax uptake
V15.CC_Acct_Encmbrnc_Amt,
l_cc_acct_encmbrnc_date,
V15.CC_Acct_Encmbrnc_Status,
V15.Project_Id,
V15.Task_Id,
V15.Expenditure_Type,
V15.Expenditure_Org_Id,
V15.Expenditure_Item_Date,
V15.Last_Update_Date,
V15.Last_Updated_By,
V15.Last_Update_Login ,
V15.Creation_Date ,
V15.Created_By ,
V15.Attribute1,
V15.Attribute2,
V15.Attribute3,
V15.Attribute4,
V15.Attribute5,
V15.Attribute6,
V15.Attribute7,
V15.Attribute8,
V15.Attribute9,
V15.Attribute10 ,
V15.Attribute11,
V15.Attribute12,
V15.Attribute13,
V15.Attribute14,
V15.Attribute15,
V15.Context,
V15.CC_FUNC_WITHHELD_AMT,
V15.CC_ENT_WITHHELD_AMT,
G_FLAG,
V15.tax_classif_code -- Bug 6472296 EB Tax uptake
);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINES_UPDATE');
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(
'Y',
'F',
V14.CC_HEADER_ID,
V15.CC_ACCT_LINE_ID,
NULL,
l_EXCEPTION,
V14.ORG_ID,
p_SOB_ID,
p_REQUEST_ID);
SELECT cc_det_pf_func_amt,
cc_det_pf_func_billed_amt
INTO l_func_amt,
l_func_billed_amt
FROM igc_cc_det_pf_v
WHERE cc_det_pf_line_id = V16.cc_det_pf_line_id;
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_DET_PF_LINE_HISTORY_ROWID,
V16.CC_Det_PF_Line_Id,
V16.CC_Det_PF_Line_Num,
V16.CC_Acct_Line_Id,
V16.Parent_Acct_Line_Id,
V16.Parent_Det_PF_Line_Id,
l_CC_VERSION_NUM,
'U',
V16.CC_Det_PF_Entered_Amt,
V16.CC_Det_PF_Func_Amt,
V16.CC_Det_PF_Date,
V16.CC_Det_PF_Billed_Amt,
V16.CC_Det_PF_Unbilled_Amt,
V16.CC_Det_PF_Encmbrnc_Amt,
V16.CC_Det_PF_Encmbrnc_Date,
V16.CC_Det_PF_Encmbrnc_Status,
V16.Last_Update_Date,
V16.Last_Updated_By,
V16.Last_Update_Login,
V16.Creation_Date,
V16.Created_By,
V16.Attribute1,
V16.Attribute2,
V16.Attribute3,
V16.Attribute4,
V16.Attribute5,
V16.Attribute6,
V16.Attribute7,
V16.Attribute8,
V16.Attribute9,
V16.Attribute10,
V16.Attribute11,
V16.Attribute12,
V16.Attribute13,
V16.Attribute14,
V16.Attribute15,
V16.Context,
G_FLAG );
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_DET_PF_HST_INSERT');
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(
'Y',
'F',
V14.CC_HEADER_ID,
V15.CC_ACCT_LINE_ID,
V16.CC_Det_PF_Line_Id,
l_EXCEPTION,
V14.ORG_ID,
p_SOB_ID,
p_REQUEST_ID);
SELECT ROWID INTO l_DET_PF_LINE_ROWID
FROM IGC_CC_DET_PF D
WHERE D.CC_DET_PF_LINE_ID = V16.CC_DET_PF_LINE_ID
AND D.CC_ACCT_LINE_ID = V15.CC_ACCT_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_DET_PF_LINE_ROWID,
V16.CC_Det_PF_Line_Id,
V16.CC_Det_PF_Line_Num,
V16.CC_Acct_Line_Id,
V16.Parent_Acct_Line_Id,
V16.Parent_Det_PF_Line_Id,
V16.CC_Det_PF_Entered_Amt,
V16.CC_Det_PF_Func_Amt,
l_next_yr_start_date,
V16.CC_Det_PF_Billed_Amt,
V16.CC_Det_PF_Unbilled_Amt,
V16.CC_Det_PF_Encmbrnc_Amt,
l_cc_det_pf_encmbrnc_date,
V16.CC_Det_PF_Encmbrnc_Status,
V16.Last_Update_Date,
V16.Last_Updated_By,
V16.Last_Update_Login,
V16.Creation_Date,
V16.Created_By,
V16.Attribute1,
V16.Attribute2,
V16.Attribute3,
V16.Attribute4,
V16.Attribute5,
V16.Attribute6,
V16.Attribute7,
V16.Attribute8,
V16.Attribute9,
V16.Attribute10,
V16.Attribute11,
V16.Attribute12,
V16.Attribute13,
V16.Attribute14,
V16.Attribute15,
V16.Context,
G_FLAG );
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_DET_PF_UPDATE');
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(
'Y',
'F',
V14.CC_HEADER_ID,
V15.CC_ACCT_LINE_ID,
V16.CC_Det_PF_Line_Id,
l_EXCEPTION,
V14.ORG_ID,
p_SOB_ID,
p_REQUEST_ID);
SELECT ROWID INTO l_HEADERS_ROWID
FROM IGC_CC_HEADERS A
WHERE A.CC_HEADER_ID = V14.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_HEADERS_ROWID,
V14.CC_HEADER_ID,
V14.ORG_ID,
V14.CC_TYPE,
V14.CC_NUM,
l_CC_VERSION_NUM + 1 ,
V14.PARENT_HEADER_ID,
V14.CC_STATE,
V14.CC_CTRL_STATUS,
V14.CC_ENCMBRNC_STATUS,
l_APPROVAL_STATUS,
V14.VENDOR_ID,
V14.VENDOR_SITE_ID,
V14.VENDOR_CONTACT_ID,
V14.TERM_ID,
V14.LOCATION_ID,
V14.SET_OF_BOOKS_ID,
l_CC_ACCT_DATE,
V14.CC_DESC,
V14.CC_START_DATE,
V14.CC_END_DATE,
V14.CC_OWNER_USER_ID,
V14.CC_PREPARER_USER_ID,
V14.CURRENCY_CODE,
V14.CONVERSION_TYPE,
V14.CONVERSION_DATE,
V14.CONVERSION_RATE,
V14.LAST_UPDATE_DATE,
V14.LAST_UPDATED_BY,
V14.LAST_UPDATE_LOGIN,
V14.CREATED_BY,
V14.CREATION_DATE,
V14.CC_CURRENT_USER_ID,
V14.WF_ITEM_TYPE,
V14.WF_ITEM_KEY,
V14.ATTRIBUTE1,
V14.ATTRIBUTE2,
V14.ATTRIBUTE3,
V14.ATTRIBUTE4,
V14.ATTRIBUTE5,
V14.ATTRIBUTE6,
V14.ATTRIBUTE7,
V14.ATTRIBUTE8,
V14.ATTRIBUTE9,
V14.ATTRIBUTE10,
V14.ATTRIBUTE11,
V14.ATTRIBUTE12,
V14.ATTRIBUTE13,
V14.ATTRIBUTE14,
V14.ATTRIBUTE15,
V14.CONTEXT,
V14.CC_GUARANTEE_FLAG,
G_FLAG);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_HEADERS_UPDATE');
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(
'Y',
'F',
V14.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V14.ORG_ID,
p_SOB_ID,
p_REQUEST_ID);
/* Insert into Action History */
l_init_msg_list := FND_API.G_FALSE;
IGC_CC_ACTIONS_PKG.Insert_Row(
1.0,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_ACTION_ROWID,
V14.CC_HEADER_ID,
NVL(l_CC_VERSION_NUM,0) + 1,
'YP',
V14.CC_STATE,
V14.CC_CTRL_STATUS,
l_APPROVAL_STATUS,
l_action_hist_msg,
Sysdate,
l_Last_Updated_By,
l_Last_Update_Login,
Sysdate,
l_Created_By);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACTION_HST_INSERT');
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(
'Y',
'F',
V14.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
V14.ORG_ID,
p_SOB_ID,
p_REQUEST_ID);
END YEAR_END_UPDATE;
End of UPDATE_CC Procedure
=================================================================================*/
/*==================================================================================
Procedure YEAR_END_MAIN
=================================================================================*/
PROCEDURE YEAR_END_MAIN ( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
/* Bug No : 6341012. MOAC uptake. SOB_ID, ORG_ID are no more retrieved from profile values in R12 */
-- p_SOB_ID IN NUMBER,
-- p_ORG_ID IN NUMBER,
p_PROCESS_PHASE IN VARCHAR2,
p_YEAR IN NUMBER)
AS
l_REQUEST_ID1 NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
SELECT *
FROM IGC_CC_HEADERS A
WHERE (( A.CC_STATE = 'PR' )
OR ( A.CC_STATE = 'CM' )
OR ( A.CC_STATE = 'CT' AND A.CC_APPRVL_STATUS <> 'AP' )
OR ( A.CC_STATE = 'CL' AND A.CC_APPRVL_STATUS <> 'AP' ) )
AND ( A.CC_END_DATE > END_DATE OR A.CC_END_DATE IS NULL)
AND A.SET_OF_BOOKS_ID = c_SOB_ID
AND A.ORG_ID = c_ORG_ID
AND EXISTS
(
SELECT 'X'
FROM IGC_CC_ACCT_LINES B,
IGC_CC_DET_PF C
WHERE B.CC_ACCT_LINE_ID = C.CC_ACCT_LINE_ID
AND B.CC_HEADER_ID = A.CC_HEADER_ID
AND C.CC_DET_PF_DATE BETWEEN START_DATE AND END_DATE );
SELECT *
FROM IGC_CC_PROCESS_DATA X
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
WHERE X.SET_OF_BOOKS_ID = l_sob_id
AND REQUEST_ID = l_REQUEST_ID1
AND X.ORG_ID = l_org_id
AND X.PROCESS_TYPE = 'Y'
AND (X.PROCESSED <> 'Y' OR X.PROCESSED IS NULL);
SELECT B.PERIOD_NUM, B.PERIOD_NAME , A.CC_PERIOD_STATUS
FROM IGC_CC_PERIODS A,
GL_PERIODS_V B,
GL_SETS_OF_BOOKS C
WHERE B.PERIOD_YEAR = p_YEAR
AND A.PERIOD_SET_NAME = B.PERIOD_SET_NAME
AND B.PERIOD_SET_NAME = C.PERIOD_SET_NAME
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND C.SET_OF_BOOKS_ID = l_sob_id
AND B.PERIOD_TYPE = C.ACCOUNTED_PERIOD_TYPE
AND A.ORG_ID = l_org_id
AND A.PERIOD_NAME = B.PERIOD_NAME
AND ADJUSTMENT_PERIOD_FLAG = 'N';
SELECT B.PERIOD_NUM, B.PERIOD_NAME , A.CC_PERIOD_STATUS
FROM IGC_CC_PERIODS A,
GL_PERIODS_V B,
GL_SETS_OF_BOOKS C
WHERE B.PERIOD_YEAR = p_YEAR+1
AND A.PERIOD_SET_NAME = B.PERIOD_SET_NAME
AND B.PERIOD_SET_NAME = C.PERIOD_SET_NAME
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND C.SET_OF_BOOKS_ID = l_sob_id
AND B.PERIOD_TYPE = C.ACCOUNTED_PERIOD_TYPE
AND A.ORG_ID = l_org_id
AND A.PERIOD_NAME = B.PERIOD_NAME
AND ADJUSTMENT_PERIOD_FLAG = 'N';
SELECT PERIOD_NAME, PERIOD_NUM, CLOSING_STATUS
FROM GL_PERIOD_STATUSES
WHERE APPLICATION_ID = (SELECT APPLICATION_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'SQLGL')
-- Bug No : 6341012. MOAC Uptake. p_sob_id is changed to l_sob_id
AND SET_OF_BOOKS_ID = l_sob_id
AND ADJUSTMENT_PERIOD_FLAG = 'N'
AND PERIOD_YEAR = p_year
AND PERIOD_NUM = p_period_num;
SELECT PERIOD_NAME, PERIOD_NUM, CLOSING_STATUS
FROM GL_PERIOD_STATUSES
WHERE APPLICATION_ID = (SELECT APPLICATION_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'SQLGL')
AND PERIOD_YEAR = p_YEAR
-- Bug No : 6341012. MOAC Uptake. p_sob_id is changed to l_sob_id
AND SET_OF_BOOKS_ID = l_sob_id
AND ADJUSTMENT_PERIOD_FLAG = 'N'
AND PERIOD_NUM = 12;
SELECT *
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID IN (SELECT IGC_CC_HEADERS.CC_HEADER_ID
FROM IGC_CC_HEADERS,IGC_CC_PROCESS_DATA
WHERE IGC_CC_HEADERS.PARENT_HEADER_ID = H_ID
AND IGC_CC_HEADERS.CC_HEADER_ID = IGC_CC_PROCESS_DATA.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id);
l_RESULT_OF_YEAR_END_UPDATE IGC_CC_PROCESS_DATA.PROCESSED%TYPE;
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
( 'Y',
p_process_phase,
NULL,
NULL,
NULL,
l_usr_msg,
/* Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
l_org_id,
l_sob_id,
l_request_id1);
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL';
SELECT min(gp.period_num)
INTO l_min_period_num
FROM gl_period_statuses gps,
gl_periods gp,
gl_sets_of_books gb
WHERE
gb.set_of_books_id = l_sob_id AND /*p_sob_id => l_sob_id by Bug 6341012 */
gp.period_set_name = gb.period_set_name AND
gp.period_type = gb.accounted_period_type AND
gps.set_of_books_id = gb.set_of_books_id AND
gps.period_name = gp.period_name AND
gps.application_id = l_application_id AND
gp.period_year = p_year+1 AND
gp.adjustment_Period_flag = 'N';
SELECT max(gp.period_num)
INTO l_max_period_num
FROM gl_period_statuses gps,
gl_periods gp,
gl_sets_of_books gb
WHERE
gb.set_of_books_id = l_sob_id AND /* p_sob_id => l_sob_id by Bug 6341012 */
gp.period_set_name = gb.period_set_name AND
gp.period_type = gb.accounted_period_type AND
gps.set_of_books_id = gb.set_of_books_id AND
gps.period_name = gp.period_name AND
gps.application_id = l_application_id AND
gp.period_year = p_year AND
gp.adjustment_Period_flag = 'N';
SELECT gps.end_date,gps.end_date INTO
l_yr_end_cr_date,l_yr_end_date
FROM gl_period_statuses gps,
gl_periods gp,
gl_sets_of_books gb
WHERE
gb.set_of_books_id = l_sob_id AND /* p_sob_id => l_sob_id by Bug 6341012 */
gp.period_set_name = gb.period_set_name AND
gp.period_type = gb.accounted_period_type AND
gps.set_of_books_id = gb.set_of_books_id AND
gps.period_name = gp.period_name AND
gps.application_id = l_application_id AND
gp.period_year = p_year AND
gp.period_num = l_max_period_num;
SELECT gps.start_date, gps.start_date INTO
l_yr_end_dr_date, l_yr_start_date_next
FROM gl_period_statuses gps,
gl_periods gp,
gl_sets_of_books gb
WHERE
gb.set_of_books_id = l_sob_id AND /* p_sob_id => l_sob_id by Bug 6341012 */
gp.period_set_name = gb.period_set_name AND
gp.period_type = gb.accounted_period_type AND
gps.set_of_books_id = gb.set_of_books_id AND
gps.period_name = gp.period_name AND
gps.application_id = l_application_id AND
gp.period_year = p_year+1 AND
gp.period_num = l_min_period_num;
SELECT gps.start_date INTO
l_yr_start_date
FROM gl_period_statuses gps,
gl_periods gp,
gl_sets_of_books gb
WHERE
gb.set_of_books_id = l_sob_id AND /* p_sob_id => l_sob_id by Bug 6341012 */
gp.period_set_name = gb.period_set_name AND
gp.period_type = gb.accounted_period_type AND
gps.set_of_books_id = gb.set_of_books_id AND
gps.period_name = gp.period_name AND
gps.application_id = l_application_id AND
gp.period_year = p_year AND
gp.period_num = l_min_period_num;
DELETE FROM IGC_CC_PROCESS_DATA A
WHERE A.PROCESS_TYPE = 'Y'
AND A.PROCESS_PHASE = 'P'
/* Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
AND A.ORG_ID = l_org_id
AND A.SET_OF_BOOKS_ID = l_sob_id;
DELETE FROM IGC_CC_PROCESS_EXCEPTIONS B
WHERE B.PROCESS_TYPE = 'Y'
/* Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
AND B.ORG_ID = l_org_id
AND B.SET_OF_BOOKS_ID = l_sob_id;
DELETE FROM IGC_CC_PROCESS_DATA A
WHERE A.PROCESS_TYPE = 'Y'
AND A.PROCESS_PHASE IN ('F','P')
AND ( A.PROCESSED <> 'Y' OR A.PROCESSED IS NULL)
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND A.ORG_ID = l_org_id
AND A.SET_OF_BOOKS_ID = l_sob_id;
UPDATE IGC_CC_PROCESS_DATA A
SET REQUEST_ID = l_REQUEST_ID1
WHERE A.PROCESS_TYPE = 'Y'
AND A.PROCESS_PHASE = 'F'
AND A.PROCESSED = 'Y'
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND A.ORG_ID = l_org_id
AND A.SET_OF_BOOKS_ID = l_sob_id;
DELETE FROM IGC_CC_PROCESS_DATA A
WHERE A.PROCESS_TYPE = 'Y'
AND A.PROCESS_PHASE = 'F'
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND A.ORG_ID = l_org_id
AND A.SET_OF_BOOKS_ID = l_sob_id
AND A.PROCESSED = 'Y';
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,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
l_org_id,
l_sob_id,
l_REQUEST_ID1);
SELECT CC_HEADER_ID
INTO l_HEADER_ID
FROM IGC_CC_PROCESS_DATA A
WHERE A.CC_HEADER_ID = V1.CC_HEADER_ID
AND A.PROCESS_PHASE = 'F'
AND A.PROCESS_TYPE = 'Y';
UPDATE IGC_CC_PROCESS_DATA A
SET OLD_APPROVAL_STATUS = V1.CC_APPRVL_STATUS,
REQUEST_ID = l_REQUEST_ID1
WHERE A.CC_HEADER_ID = V1.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND A.ORG_ID = l_org_id
AND A.SET_OF_BOOKS_ID = l_sob_id
AND A.PROCESS_PHASE = 'F'
AND A.PROCESS_TYPE = 'Y';
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,
NULL,
NULL,
NULL,
V1.CC_APPRVL_STATUS,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
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,
NULL,
NULL,
NULL,
V1.CC_APPRVL_STATUS,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
l_org_id,
l_sob_id,
NULL,
l_REQUEST_ID1);
SELECT CC_TYPE
INTO l_Type
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
UPDATE IGC_CC_PROCESS_DATA
SET VALIDATION_STATUS = l_RESULT_OF_VALIDATION
WHERE IGC_CC_PROCESS_DATA.CC_HEADER_ID = V2.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE
AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1;
SELECT CC_NUM
INTO l_CC_NUM
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V2.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,
V2.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
l_org_id,
l_sob_id,
l_REQUEST_ID1);
SELECT CC_TYPE
INTO l_Type
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V2.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,
V2.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
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,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
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,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
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,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
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,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
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,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
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,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
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,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
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,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
l_org_id,
l_sob_id,
l_REQUEST_ID1);
SELECT CC_TYPE,CC_STATE,CC_APPRVL_STATUS
INTO l_TYPE,l_STATE,l_PREVIOUS_APPRVL_STATUS
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
UPDATE IGC_CC_PROCESS_DATA A
SET VALIDATION_STATUS = 'P'
WHERE A.CC_HEADER_ID = V2.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND A.ORG_ID = l_org_id
AND A.SET_OF_BOOKS_ID = l_sob_id
AND A.REQUEST_ID = l_REQUEST_ID1
AND A.PROCESS_TYPE = l_PROCESS_TYPE;
UPDATE IGC_CC_HEADERS
SET CC_APPRVL_STATUS = 'IP'
WHERE IGC_CC_HEADERS.CC_HEADER_ID = V2.CC_HEADER_ID;
SELECT 'Y'
INTO l_DUMMY
FROM PO_HEADERS_ALL A
WHERE
A.PO_HEADER_ID = (SELECT C.PO_HEADER_ID
FROM IGC_CC_HEADERS B,
PO_HEADERS_ALL C
WHERE B.ORG_ID = C.ORG_ID AND
B.CC_NUM = C.SEGMENT1 AND
C.TYPE_LOOKUP_CODE = 'STANDARD' AND
B.CC_HEADER_ID = V2.CC_HEADER_ID );
UPDATE PO_HEADERS_ALL
SET APPROVED_FLAG = 'N'
WHERE (SEGMENT1,ORG_ID,TYPE_LOOKUP_CODE) IN ( SELECT SEGMENT1,a.ORG_ID,TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL a, IGC_CC_HEADERS b
WHERE a.SEGMENT1 = b.CC_NUM
AND a.ORG_ID = b.ORG_ID
AND a.TYPE_LOOKUP_CODE = 'STANDARD'
AND b.CC_HEADER_ID = V2.CC_HEADER_ID);
UPDATE IGC_CC_PROCESS_DATA
SET VALIDATION_STATUS = 'F'
WHERE IGC_CC_PROCESS_DATA.CC_HEADER_ID = V2.CC_HEADER_ID
AND REQUEST_ID = l_REQUEST_ID1;
SELECT COUNT(*)
INTO l_VALIDATION_COUNTER
FROM IGC_CC_PROCESS_DATA
WHERE VALIDATION_STATUS = 'F'
AND REQUEST_ID = l_REQUEST_ID1
AND PROCESS_TYPE = 'Y'
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND SET_OF_BOOKS_ID = l_sob_id
AND ORG_ID = l_org_id;
SELECT COUNT(*)
INTO l_invalid_counter
FROM IGC_CC_PROCESS_DATA
WHERE validation_status = 'F'
AND request_id = l_REQUEST_ID1
AND process_type = 'Y'
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND set_of_books_id = l_sob_id
AND org_id = l_org_id
AND cc_header_id = V2.cc_header_id;
SELECT CC_STATE
INTO l_STATE
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
UPDATE IGC_CC_PROCESS_DATA
SET RESERVATION_STATUS ='P'
WHERE IGC_CC_PROCESS_DATA.CC_HEADER_ID = V2.CC_HEADER_ID
AND REQUEST_ID = l_REQUEST_ID1;
SELECT CC_TYPE
INTO l_CC_TYPE
FROM IGC_CC_HEADERS
WHERE IGC_CC_HEADERS.CC_HEADER_ID = V2.CC_HEADER_ID;
l_RESULT_OF_YEAR_END_UPDATE := YEAR_END_UPDATE(V2.CC_HEADER_ID,
p_YEAR,
-- Bug No : 6341012. MOAC Uptake. p_sob_id is changed to l_sob_id
l_sob_id,
l_REQUEST_ID1,
l_yr_start_date,
l_yr_end_date,
l_sbc_on,
l_cbc_on,
l_prov_enc_on,
l_conf_enc_on);
IF l_RESULT_OF_YEAR_END_UPDATE = 'Y'
THEN
UPDATE IGC_CC_PROCESS_DATA
SET PROCESSED = 'Y'
WHERE CC_HEADER_ID = V2.CC_HEADER_ID
AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
UPDATE IGC_CC_HEADERS
SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
SELECT CC_STATE
INTO l_STATE
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
SELECT 'Y'
INTO l_DUMMY
FROM PO_HEADERS_ALL A
WHERE
A.PO_HEADER_ID = (SELECT C.PO_HEADER_ID
FROM IGC_CC_HEADERS B,
PO_HEADERS_ALL C
WHERE B.ORG_ID = C.ORG_ID AND
B.CC_NUM = C.SEGMENT1 AND
C.TYPE_LOOKUP_CODE = 'STANDARD' AND
B.CC_HEADER_ID = V2.CC_HEADER_ID );
UPDATE IGC_CC_PROCESS_DATA
SET PROCESSED = 'N',
VALIDATION_STATUS = 'F',
RESERVATION_STATUS = 'F'
WHERE CC_HEADER_ID = V2.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE ;
UPDATE IGC_CC_HEADERS
SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_HEADERS.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_HEADERS.ORG_ID = l_org_id;
l_RESULT_OF_YEAR_END_UPDATE := YEAR_END_UPDATE(V2.CC_HEADER_ID,
p_YEAR,
-- Bug No : 6341012. MOAC Uptake. p_sob_id is changed to l_sob_id
l_sob_id,
l_REQUEST_ID1,
l_yr_start_date,
l_yr_end_date,
l_sbc_on,
l_cbc_on,
l_prov_enc_on,
l_conf_enc_on);
IF l_RESULT_OF_YEAR_END_UPDATE = 'Y'
THEN
UPDATE IGC_CC_PROCESS_DATA
SET PROCESSED = 'Y'
WHERE CC_HEADER_ID = V2.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
l_RESULT_OF_YEAR_END_UPDATE := YEAR_END_UPDATE(V11.CC_HEADER_ID,
p_YEAR,
-- Bug No : 6341012. MOAC Uptake. p_sob_id is changed to l_sob_id
l_sob_id,
l_REQUEST_ID1,
l_yr_start_date,
l_yr_end_date,
l_sbc_on,
l_cbc_on,
l_prov_enc_on,
l_conf_enc_on);
IF l_RESULT_OF_YEAR_END_UPDATE = 'N'
THEN
RELEASE_YEAR_END_COUNTER := RELEASE_YEAR_END_COUNTER +1;
UPDATE IGC_CC_PROCESS_DATA
SET PROCESSED = l_RESULT_OF_YEAR_END_UPDATE
WHERE CC_HEADER_ID = V11.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
UPDATE IGC_CC_PROCESS_DATA
SET PROCESSED = 'N'
WHERE CC_HEADER_ID = V2.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_COVER_REL_INSERT');
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,
V2.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
l_org_id,
l_sob_id,
l_REQUEST_ID1);
UPDATE IGC_CC_HEADERS
SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
SELECT CC_STATE
INTO l_STATE
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
SELECT 'Y'
INTO l_DUMMY
FROM PO_HEADERS_ALL A
WHERE
A.PO_HEADER_ID =
(SELECT C.PO_HEADER_ID
FROM IGC_CC_HEADERS B,
PO_HEADERS_ALL C
WHERE B.ORG_ID = C.ORG_ID AND
B.CC_NUM = C.SEGMENT1 AND
C.TYPE_LOOKUP_CODE = 'STANDARD' AND
B.CC_HEADER_ID = V11.CC_HEADER_ID );
ELSE UPDATE IGC_CC_PROCESS_DATA
SET PROCESSED = 'N',
VALIDATION_STATUS = 'F',
RESERVATION_STATUS = 'F'
WHERE CC_HEADER_ID = V2.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
UPDATE IGC_CC_HEADERS
SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_HEADERS.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_HEADERS.ORG_ID = l_org_id;
UPDATE IGC_CC_PROCESS_DATA
SET PROCESSED = 'N'
WHERE CC_HEADER_ID = V11.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
SELECT CC_NUM
INTO l_CC_NUM
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V2.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,
V2.CC_HEADER_ID,
NULL,
NULL,
l_EXCEPTION,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
l_org_id,
l_sob_id,
l_REQUEST_ID1);
UPDATE IGC_CC_PROCESS_DATA
SET VALIDATION_STATUS = 'F',
PROCESSED = 'N',
RESERVATION_STATUS = 'F'
WHERE CC_HEADER_ID = V2.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
UPDATE IGC_CC_PROCESS_DATA
SET PROCESSED = 'N',
VALIDATION_STATUS = 'F',
RESERVATION_STATUS = 'F'
WHERE CC_HEADER_ID = V2.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE ;
UPDATE IGC_CC_HEADERS
SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND IGC_CC_HEADERS.SET_OF_BOOKS_ID = l_sob_id
AND IGC_CC_HEADERS.ORG_ID = l_org_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,
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
l_org_id,
l_sob_id,
l_REQUEST_ID1);
SELECT COUNT(*)
INTO l_PROCESSED_COUNTER
FROM IGC_CC_PROCESS_DATA
WHERE PROCESSED = 'N'
AND PROCESS_TYPE = l_PROCESS_TYPE
-- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
AND ORG_ID = l_org_id
AND REQUEST_ID = l_REQUEST_ID1
AND SET_OF_BOOKS_ID = l_sob_id ;
UPDATE IGC_CC_HEADERS
SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
SELECT CC_CTRL_STATUS
INTO l_CC_CTRL_STATUS
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
SELECT CC_TYPE,CC_STATE,CC_APPRVL_STATUS
INTO l_TYPE,l_STATE,l_PREVIOUS_APPRVL_STATUS
FROM IGC_CC_HEADERS
WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
SELECT 'Y'
INTO l_DUMMY
FROM PO_HEADERS_ALL A
WHERE
A.PO_HEADER_ID = (SELECT C.PO_HEADER_ID
FROM IGC_CC_HEADERS B,
PO_HEADERS_ALL C
WHERE B.ORG_ID = C.ORG_ID AND
B.CC_NUM = C.SEGMENT1 AND
C.TYPE_LOOKUP_CODE = 'STANDARD' AND
B.CC_HEADER_ID = V2.CC_HEADER_ID );
UPDATE PO_HEADERS_ALL
SET APPROVED_FLAG = l_APPROVED_FLAG
WHERE (SEGMENT1,ORG_ID,TYPE_LOOKUP_CODE) IN ( SELECT SEGMENT1,a.ORG_ID,TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL a, IGC_CC_HEADERS b
WHERE a.SEGMENT1 = b.CC_NUM
AND a.ORG_ID = b.ORG_ID
AND a.TYPE_LOOKUP_CODE = 'STANDARD'
AND b.CC_HEADER_ID = V2.CC_HEADER_ID);