[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_update_login igc_cc_headers.last_update_login%TYPE;
g_update_by igc_cc_headers.last_updated_by%TYPE;
PROCEDURE Insert_Archive_History (
x_Return_Status OUT NOCOPY VARCHAR2
);
PROCEDURE Update_History
(
p_History_Rec IN igc_cc_archive_history%ROWTYPE, -- History Record
x_Return_Status OUT NOCOPY VARCHAR2 -- Status of procedure
);
SELECT meaning
INTO l_option_name
FROM igi_lookups
WHERE lookup_code = 'CC'
AND lookup_type = 'GCC_DESCRIPTION';
g_update_login := FND_GLOBAL.LOGIN_ID;
g_update_by := FND_GLOBAL.USER_ID;
/* Select set_of_books_id into g_sob_id
FROM hr_operating_units WHERE organization_id = p_org_id;*/
DELETE
FROM igc_arc_pur_candidates;
Insert_Archive_History (x_return_status => l_return_status);
INSERT
INTO igc_cc_arc_mc_headers
(CC_HEADER_ID,
SET_OF_BOOKS_ID,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE)
SELECT
CC_HEADER_ID,
SET_OF_BOOKS_ID,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE
FROM igc_cc_mc_headers CMH
WHERE CMH.cc_header_id IN
( SELECT ICV.cc_header_id
FROM igc_arc_pur_candidates ICV
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in MC Headers Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_mc_header_hist
(CC_HEADER_ID,
SET_OF_BOOKS_ID,
CC_VERSION_NUM,
CC_VERSION_ACTION,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE)
SELECT
CC_HEADER_ID,
SET_OF_BOOKS_ID,
CC_VERSION_NUM,
CC_VERSION_ACTION,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE
FROM igc_cc_mc_header_history CMHH
WHERE CMHH.cc_header_id IN
( SELECT ICV.cc_header_id
FROM igc_arc_pur_candidates ICV
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in MC Headers Hist Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_mc_acct_lines
(CC_ACCT_LINE_ID,
SET_OF_BOOKS_ID,
CC_ACCT_FUNC_AMT ,
CC_ACCT_ENCMBRNC_AMT,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE,
CC_FUNC_WITHHELD_AMT)
SELECT
CC_ACCT_LINE_ID,
SET_OF_BOOKS_ID,
CC_ACCT_FUNC_AMT ,
CC_ACCT_ENCMBRNC_AMT,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE,
CC_FUNC_WITHHELD_AMT
FROM igc_cc_mc_acct_lines CML
WHERE CML.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR CML.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in MC Acct Line Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_mc_acct_line_hist
( CC_ACCT_LINE_ID,
SET_OF_BOOKS_ID,
CC_ACCT_FUNC_AMT,
CC_ACCT_ENCMBRNC_AMT,
CC_ACCT_VERSION_NUM,
CC_ACCT_VERSION_ACTION,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE,
CC_FUNC_WITHHELD_AMT)
SELECT
CC_ACCT_LINE_ID,
SET_OF_BOOKS_ID,
CC_ACCT_FUNC_AMT,
CC_ACCT_ENCMBRNC_AMT,
CC_ACCT_VERSION_NUM,
CC_ACCT_VERSION_ACTION,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE,
CC_FUNC_WITHHELD_AMT
FROM igc_cc_mc_acct_line_history CMLH
WHERE CMLH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR CMLH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in MC Acct Line Hist Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_mc_det_pf
(CC_DET_PF_LINE_ID,
SET_OF_BOOKS_ID,
CC_DET_PF_FUNC_AMT ,
CC_DET_PF_ENCMBRNC_AMT,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE )
SELECT
CC_DET_PF_LINE_ID,
SET_OF_BOOKS_ID,
CC_DET_PF_FUNC_AMT ,
CC_DET_PF_ENCMBRNC_AMT,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE
FROM igc_cc_mc_det_pf CMDP
WHERE CMDP.cc_det_pf_line_id IN
( SELECT DPFH.cc_det_pf_line_id
FROM igc_cc_det_pf_history DPFH
WHERE DPFH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR DPFH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
)
)
OR CMDP.cc_det_pf_line_id IN
( SELECT DPF.cc_det_pf_line_id
FROM igc_cc_det_pf DPF
WHERE DPF.cc_acct_line_id IN
( SELECT ACLH1.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH1
WHERE ACLH1.cc_header_id IN
( SELECT ICV3.cc_header_id
FROM igc_arc_pur_candidates ICV3
)
)
OR DPF.cc_acct_line_id IN
( SELECT ACL1.cc_acct_line_id
FROM igc_cc_acct_lines ACL1
WHERE ACL1.cc_header_id IN
( SELECT ICV4.cc_header_id
FROM igc_arc_pur_candidates ICV4
)
)
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in MC Det PF Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_mc_det_pf_hist
( CC_DET_PF_LINE_ID,
SET_OF_BOOKS_ID,
CC_DET_PF_FUNC_AMT,
CC_DET_PF_ENCMBRNC_AMT,
CC_DET_PF_VERSION_NUM ,
CC_DET_PF_VERSION_ACTION,
CONVERSION_TYPE,
CONVERSION_DATE ,
CONVERSION_RATE)
SELECT
CC_DET_PF_LINE_ID,
SET_OF_BOOKS_ID,
CC_DET_PF_FUNC_AMT,
CC_DET_PF_ENCMBRNC_AMT,
CC_DET_PF_VERSION_NUM ,
CC_DET_PF_VERSION_ACTION,
CONVERSION_TYPE,
CONVERSION_DATE ,
CONVERSION_RATE
FROM igc_cc_mc_det_pf_history CMDPH
WHERE CMDPH.cc_det_pf_line_id IN
( SELECT DPFH.cc_det_pf_line_id
FROM igc_cc_det_pf_history DPFH
WHERE DPFH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR DPFH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
)
)
OR CMDPH.cc_det_pf_line_id IN
( SELECT DPF.cc_det_pf_line_id
FROM igc_cc_det_pf DPF
WHERE DPF.cc_acct_line_id IN
( SELECT ACLH1.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH1
WHERE ACLH1.cc_header_id IN
( SELECT ICV3.cc_header_id
FROM igc_arc_pur_candidates ICV3
)
)
OR DPF.cc_acct_line_id IN
( SELECT ACL1.cc_acct_line_id
FROM igc_cc_acct_lines ACL1
WHERE ACL1.cc_header_id IN
( SELECT ICV4.cc_header_id
FROM igc_arc_pur_candidates ICV4
)
)
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into MC Det PF Hist Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_headers_all
(CC_HEADER_ID ,
ORG_ID ,
CC_TYPE ,
CC_NUM ,
CC_REF_NUM ,
CC_VERSION_NUM ,
PARENT_HEADER_ID ,
CC_STATE ,
CC_CTRL_STATUS ,
CC_ENCMBRNC_STATUS,
CC_APPRVL_STATUS ,
VENDOR_ID ,
VENDOR_SITE_ID ,
VENDOR_CONTACT_ID ,
TERM_ID ,
LOCATION_ID ,
SET_OF_BOOKS_ID ,
CC_ACCT_DATE ,
CC_DESC ,
CC_START_DATE ,
CC_END_DATE ,
CC_OWNER_USER_ID ,
CC_PREPARER_USER_ID,
CURRENCY_CODE ,
CONVERSION_TYPE ,
CONVERSION_DATE ,
CONVERSION_RATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE ,
CC_CURRENT_USER_ID ,
WF_ITEM_TYPE ,
WF_ITEM_KEY ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CC_GUARANTEE_FLAG )
SELECT
CC_HEADER_ID ,
ORG_ID ,
CC_TYPE ,
CC_NUM ,
CC_REF_NUM ,
CC_VERSION_NUM ,
PARENT_HEADER_ID ,
CC_STATE ,
CC_CTRL_STATUS ,
CC_ENCMBRNC_STATUS,
CC_APPRVL_STATUS ,
VENDOR_ID ,
VENDOR_SITE_ID ,
VENDOR_CONTACT_ID ,
TERM_ID ,
LOCATION_ID ,
SET_OF_BOOKS_ID ,
CC_ACCT_DATE ,
CC_DESC ,
CC_START_DATE ,
CC_END_DATE ,
CC_OWNER_USER_ID ,
CC_PREPARER_USER_ID,
CURRENCY_CODE ,
CONVERSION_TYPE ,
CONVERSION_DATE ,
CONVERSION_RATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE ,
CC_CURRENT_USER_ID ,
WF_ITEM_TYPE ,
WF_ITEM_KEY ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CC_GUARANTEE_FLAG
FROM igc_cc_headers CH
WHERE CH.cc_header_id IN
( SELECT cc_header_id
FROM igc_arc_pur_candidates
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows in Headers Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_header_hist_all
(CC_HEADER_ID ,
ORG_ID ,
CC_TYPE ,
CC_NUM ,
CC_REF_NUM ,
CC_VERSION_NUM ,
CC_VERSION_ACTION ,
CC_STATE ,
PARENT_HEADER_ID ,
CC_CTRL_STATUS ,
CC_ENCMBRNC_STATUS ,
CC_APPRVL_STATUS ,
VENDOR_ID ,
VENDOR_SITE_ID ,
VENDOR_CONTACT_ID ,
TERM_ID ,
LOCATION_ID ,
SET_OF_BOOKS_ID ,
CC_ACCT_DATE ,
CC_DESC ,
CC_START_DATE ,
CC_END_DATE ,
CC_OWNER_USER_ID ,
CC_PREPARER_USER_ID ,
CURRENCY_CODE ,
CONVERSION_TYPE ,
CONVERSION_DATE ,
CONVERSION_RATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE ,
WF_ITEM_TYPE ,
WF_ITEM_KEY ,
CC_CURRENT_USER_ID ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CC_GUARANTEE_FLAG )
SELECT
CC_HEADER_ID ,
ORG_ID ,
CC_TYPE ,
CC_NUM ,
CC_REF_NUM ,
CC_VERSION_NUM ,
CC_VERSION_ACTION ,
CC_STATE ,
PARENT_HEADER_ID ,
CC_CTRL_STATUS ,
CC_ENCMBRNC_STATUS ,
CC_APPRVL_STATUS ,
VENDOR_ID ,
VENDOR_SITE_ID ,
VENDOR_CONTACT_ID ,
TERM_ID ,
LOCATION_ID ,
SET_OF_BOOKS_ID ,
CC_ACCT_DATE ,
CC_DESC ,
CC_START_DATE ,
CC_END_DATE ,
CC_OWNER_USER_ID ,
CC_PREPARER_USER_ID ,
CURRENCY_CODE ,
CONVERSION_TYPE ,
CONVERSION_DATE ,
CONVERSION_RATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE ,
WF_ITEM_TYPE ,
WF_ITEM_KEY ,
CC_CURRENT_USER_ID ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CC_GUARANTEE_FLAG
FROM igc_cc_header_history CHH
WHERE CHH.cc_header_id IN
( SELECT cc_header_id
FROM igc_arc_pur_candidates
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Headers Hist Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_acct_lines
( CC_ACCT_LINE_ID ,
CC_HEADER_ID ,
PARENT_HEADER_ID ,
PARENT_ACCT_LINE_ID ,
CC_CHARGE_CODE_COMBINATION_ID ,
CC_ACCT_LINE_NUM ,
CC_BUDGET_CODE_COMBINATION_ID ,
CC_ACCT_ENTERED_AMT ,
CC_ACCT_FUNC_AMT ,
CC_ACCT_DESC ,
CC_ACCT_BILLED_AMT ,
CC_ACCT_UNBILLED_AMT ,
CC_ACCT_TAXABLE_FLAG ,
TAX_ID ,
CC_ACCT_ENCMBRNC_AMT ,
CC_ACCT_ENCMBRNC_DATE ,
CC_ACCT_ENCMBRNC_STATUS ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
EXPENDITURE_ORG_ID ,
EXPENDITURE_ITEM_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CC_FUNC_WITHHELD_AMT ,
CC_ENT_WITHHELD_AMT ,
TAX_CLASSIF_CODE -- Added for Bug 6472296 EB Tax uptake
)
SELECT
CC_ACCT_LINE_ID ,
CC_HEADER_ID ,
PARENT_HEADER_ID ,
PARENT_ACCT_LINE_ID ,
CC_CHARGE_CODE_COMBINATION_ID ,
CC_ACCT_LINE_NUM ,
CC_BUDGET_CODE_COMBINATION_ID ,
CC_ACCT_ENTERED_AMT ,
CC_ACCT_FUNC_AMT ,
CC_ACCT_DESC ,
CC_ACCT_BILLED_AMT ,
CC_ACCT_UNBILLED_AMT ,
CC_ACCT_TAXABLE_FLAG ,
TAX_ID ,
CC_ACCT_ENCMBRNC_AMT ,
CC_ACCT_ENCMBRNC_DATE ,
CC_ACCT_ENCMBRNC_STATUS ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
EXPENDITURE_ORG_ID ,
EXPENDITURE_ITEM_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CC_FUNC_WITHHELD_AMT ,
CC_ENT_WITHHELD_AMT ,
TAX_CLASSIF_CODE -- Added for Bug 6472296 EB Tax uptake
FROM igc_cc_acct_lines CL
WHERE CL.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Acct Line Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_acct_line_hist
( CC_ACCT_LINE_ID ,
CC_HEADER_ID ,
PARENT_HEADER_ID ,
PARENT_ACCT_LINE_ID ,
CC_ACCT_LINE_NUM ,
CC_ACCT_VERSION_NUM ,
CC_ACCT_VERSION_ACTION ,
CC_CHARGE_CODE_COMBINATION_ID ,
CC_BUDGET_CODE_COMBINATION_ID ,
CC_ACCT_ENTERED_AMT ,
CC_ACCT_FUNC_AMT ,
CC_ACCT_DESC ,
CC_ACCT_BILLED_AMT ,
CC_ACCT_UNBILLED_AMT ,
CC_ACCT_TAXABLE_FLAG ,
TAX_ID ,
CC_ACCT_ENCMBRNC_AMT ,
CC_ACCT_ENCMBRNC_DATE ,
CC_ACCT_ENCMBRNC_STATUS ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
EXPENDITURE_ORG_ID ,
EXPENDITURE_ITEM_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CC_FUNC_WITHHELD_AMT ,
CC_ENT_WITHHELD_AMT ,
TAX_CLASSIF_CODE -- Added for Bug 6472296 EB Tax uptake
)
SELECT
CC_ACCT_LINE_ID ,
CC_HEADER_ID ,
PARENT_HEADER_ID ,
PARENT_ACCT_LINE_ID ,
CC_ACCT_LINE_NUM ,
CC_ACCT_VERSION_NUM ,
CC_ACCT_VERSION_ACTION ,
CC_CHARGE_CODE_COMBINATION_ID ,
CC_BUDGET_CODE_COMBINATION_ID ,
CC_ACCT_ENTERED_AMT ,
CC_ACCT_FUNC_AMT ,
CC_ACCT_DESC ,
CC_ACCT_BILLED_AMT ,
CC_ACCT_UNBILLED_AMT ,
CC_ACCT_TAXABLE_FLAG ,
TAX_ID ,
CC_ACCT_ENCMBRNC_AMT ,
CC_ACCT_ENCMBRNC_DATE ,
CC_ACCT_ENCMBRNC_STATUS ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
EXPENDITURE_ORG_ID ,
EXPENDITURE_ITEM_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CC_FUNC_WITHHELD_AMT ,
CC_ENT_WITHHELD_AMT ,
TAX_CLASSIF_CODE -- Added for Bug 6472296 EB Tax uptake
FROM igc_cc_acct_line_history CLH
WHERE CLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Acct Line Hist Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_det_pf
(CC_DET_PF_LINE_ID ,
CC_DET_PF_LINE_NUM ,
CC_ACCT_LINE_ID ,
PARENT_ACCT_LINE_ID ,
PARENT_DET_PF_LINE_ID ,
CC_DET_PF_ENTERED_AMT ,
CC_DET_PF_FUNC_AMT ,
CC_DET_PF_DATE ,
CC_DET_PF_BILLED_AMT ,
CC_DET_PF_UNBILLED_AMT ,
CC_DET_PF_ENCMBRNC_AMT ,
CC_DET_PF_ENCMBRNC_DATE ,
CC_DET_PF_ENCMBRNC_STATUS,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 )
SELECT
CC_DET_PF_LINE_ID ,
CC_DET_PF_LINE_NUM ,
CC_ACCT_LINE_ID ,
PARENT_ACCT_LINE_ID ,
PARENT_DET_PF_LINE_ID ,
CC_DET_PF_ENTERED_AMT ,
CC_DET_PF_FUNC_AMT ,
CC_DET_PF_DATE ,
CC_DET_PF_BILLED_AMT ,
CC_DET_PF_UNBILLED_AMT ,
CC_DET_PF_ENCMBRNC_AMT ,
CC_DET_PF_ENCMBRNC_DATE ,
CC_DET_PF_ENCMBRNC_STATUS,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
FROM igc_cc_det_pf CDP
WHERE CDP.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR CDP.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Det PF Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_det_pf_hist
( CC_DET_PF_LINE_ID ,
CC_DET_PF_LINE_NUM ,
CC_ACCT_LINE_ID ,
PARENT_ACCT_LINE_ID ,
PARENT_DET_PF_LINE_ID ,
CC_DET_PF_VERSION_NUM ,
CC_DET_PF_VERSION_ACTION ,
CC_DET_PF_ENTERED_AMT ,
CC_DET_PF_FUNC_AMT ,
CC_DET_PF_DATE ,
CC_DET_PF_BILLED_AMT ,
CC_DET_PF_UNBILLED_AMT ,
CC_DET_PF_ENCMBRNC_AMT ,
CC_DET_PF_ENCMBRNC_DATE ,
CC_DET_PF_ENCMBRNC_STATUS ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 )
SELECT
CC_DET_PF_LINE_ID ,
CC_DET_PF_LINE_NUM ,
CC_ACCT_LINE_ID ,
PARENT_ACCT_LINE_ID ,
PARENT_DET_PF_LINE_ID ,
CC_DET_PF_VERSION_NUM ,
CC_DET_PF_VERSION_ACTION ,
CC_DET_PF_ENTERED_AMT ,
CC_DET_PF_FUNC_AMT ,
CC_DET_PF_DATE ,
CC_DET_PF_BILLED_AMT ,
CC_DET_PF_UNBILLED_AMT ,
CC_DET_PF_ENCMBRNC_AMT ,
CC_DET_PF_ENCMBRNC_DATE ,
CC_DET_PF_ENCMBRNC_STATUS ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
FROM igc_cc_det_pf_history CDPH
WHERE CDPH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR CDPH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Det PF Hist Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_actions
( CC_HEADER_ID ,
CC_ACTION_NUM ,
CC_ACTION_VERSION_NUM ,
CC_ACTION_TYPE ,
CC_ACTION_STATE ,
CC_ACTION_CTRL_STATUS ,
CC_ACTION_APPRVL_STATUS ,
CC_ACTION_NOTES ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY )
SELECT
CC_HEADER_ID ,
CC_ACTION_NUM ,
CC_ACTION_VERSION_NUM ,
CC_ACTION_TYPE ,
CC_ACTION_STATE ,
CC_ACTION_CTRL_STATUS ,
CC_ACTION_APPRVL_STATUS ,
CC_ACTION_NOTES ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY
FROM igc_cc_actions CA
WHERE CA.cc_header_id IN
( SELECT ICV.cc_header_id
FROM igc_arc_pur_candidates ICV
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into Actions Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_po_headers_all
(po_header_id,
agent_id,
type_lookup_code,
last_update_date,
last_updated_by,
segment1,
summary_flag,
enabled_flag,
segment2,
segment3,
segment4,
segment5,
start_date_active,
end_date_active,
last_update_login,
creation_date,
created_by,
vendor_id,
vendor_site_id,
vendor_contact_id,
ship_to_location_id,
bill_to_location_id,
terms_id,
ship_via_lookup_code,
fob_lookup_code,
freight_terms_lookup_code,
status_lookup_code,
currency_code,
rate_type,
rate_date,
rate,
from_header_id,
from_type_lookup_code,
start_date,
end_date,
blanket_total_amount,
authorization_status,
revision_num,
revised_date,
approved_flag,
approved_date,
amount_limit,
min_release_amount,
note_to_authorizer,
note_to_vendor,
note_to_receiver,
print_count,
printed_date,
vendor_order_num,
confirming_order_flag,
comments,
reply_date,
reply_method_lookup_code,
rfq_close_date,
quote_type_lookup_code,
quotation_class_code,
quote_warning_delay_unit,
quote_warning_delay,
quote_vendor_quote_number,
acceptance_required_flag,
acceptance_due_date,
closed_date,
user_hold_flag,
approval_required_flag,
cancel_flag,
firm_status_lookup_code,
firm_date,
frozen_flag,
supply_agreement_flag,
edi_processed_flag,
edi_processed_status,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
closed_code,
ussgl_transaction_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
org_id,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
interface_source_code,
reference_num,
wf_item_type,
wf_item_key,
mrc_rate_type,
mrc_rate_date,
mrc_rate,
pcard_id,
price_update_tolerance,
pay_on_code
)
SELECT po_header_id,
agent_id,
type_lookup_code,
last_update_date,
last_updated_by,
segment1,
summary_flag,
enabled_flag,
segment2,
segment3,
segment4,
segment5,
start_date_active,
end_date_active,
last_update_login,
creation_date,
created_by,
vendor_id,
vendor_site_id,
vendor_contact_id,
ship_to_location_id,
bill_to_location_id,
terms_id,
ship_via_lookup_code,
fob_lookup_code,
freight_terms_lookup_code,
status_lookup_code,
currency_code,
rate_type,
rate_date,
rate,
from_header_id,
from_type_lookup_code,
start_date,
end_date,
blanket_total_amount,
authorization_status,
revision_num,
revised_date,
approved_flag,
approved_date,
amount_limit,
min_release_amount,
note_to_authorizer,
note_to_vendor,
note_to_receiver,
print_count,
printed_date,
vendor_order_num,
confirming_order_flag,
comments,
reply_date,
reply_method_lookup_code,
rfq_close_date,
quote_type_lookup_code,
quotation_class_code,
quote_warning_delay_unit,
quote_warning_delay,
quote_vendor_quote_number,
acceptance_required_flag,
acceptance_due_date,
closed_date,
user_hold_flag,
approval_required_flag,
cancel_flag,
firm_status_lookup_code,
firm_date,
frozen_flag,
supply_agreement_flag,
edi_processed_flag,
edi_processed_status,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
closed_code,
ussgl_transaction_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
org_id,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
interface_source_code,
reference_num,
wf_item_type,
wf_item_key,
mrc_rate_type,
mrc_rate_date,
mrc_rate,
pcard_id,
price_update_tolerance,
pay_on_code
FROM po_headers PHA
-- ssmales 11/07/03 bug 2885953 - amended where clause below for performance issues
-- WHERE PHA.po_header_id IN
-- ( SELECT PHA1.po_header_id
-- FROM po_headers_all PHA1
-- WHERE PHA1.segment1 IN
WHERE PHA.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into PO Headers ALL Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_po_lines_all
(po_line_id,
last_update_date,
last_updated_by,
po_header_id,
line_type_id,
line_num,
last_update_login,
creation_date,
created_by,
item_id,
item_revision,
category_id,
item_description,
unit_meas_lookup_code,
quantity_committed,
committed_amount,
allow_price_override_flag,
not_to_exceed_price,
list_price_per_unit,
unit_price,
quantity,
un_number_id,
hazard_class_id,
note_to_vendor,
from_header_id,
from_line_id,
min_order_quantity,
max_order_quantity,
qty_rcv_tolerance,
over_tolerance_error_flag,
market_price,
unordered_flag,
closed_flag,
user_hold_flag,
cancel_flag,
cancelled_by,
cancel_date,
cancel_reason,
firm_status_lookup_code,
firm_date,
vendor_product_num,
contract_num,
taxable_flag,
tax_name,
type_1099,
capital_expense_flag,
negotiated_by_preparer_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
reference_num,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
min_release_amount,
price_type_lookup_code,
closed_code,
price_break_lookup_code,
ussgl_transaction_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
closed_date,
closed_reason,
closed_by,
transaction_reason_code,
org_id,
qc_grade,
base_uom,
base_qty,
secondary_uom,
secondary_qty,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
line_reference_num,
project_id,
task_id,
expiration_date,
tax_code_id
)
SELECT po_line_id,
last_update_date,
last_updated_by,
po_header_id,
line_type_id,
line_num,
last_update_login,
creation_date,
created_by,
item_id,
item_revision,
category_id,
item_description,
unit_meas_lookup_code,
quantity_committed,
committed_amount,
allow_price_override_flag,
not_to_exceed_price,
list_price_per_unit,
unit_price,
quantity,
un_number_id,
hazard_class_id,
note_to_vendor,
from_header_id,
from_line_id,
min_order_quantity,
max_order_quantity,
qty_rcv_tolerance,
over_tolerance_error_flag,
market_price,
unordered_flag,
closed_flag,
user_hold_flag,
cancel_flag,
cancelled_by,
cancel_date,
cancel_reason,
firm_status_lookup_code,
firm_date,
vendor_product_num,
contract_num,
taxable_flag,
tax_name,
type_1099,
capital_expense_flag,
negotiated_by_preparer_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
reference_num,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
min_release_amount,
price_type_lookup_code,
closed_code,
price_break_lookup_code,
ussgl_transaction_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
closed_date,
closed_reason,
closed_by,
transaction_reason_code,
org_id,
qc_grade,
base_uom,
base_qty,
secondary_uom,
secondary_qty,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
line_reference_num,
project_id,
task_id,
expiration_date,
tax_code_id
FROM po_lines PLA
WHERE PLA.po_header_id IN
( SELECT PHA.po_header_id
FROM po_headers PHA
WHERE PHA.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
)
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into PO Lines ALL Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_po_line_loc_all
(line_location_id,
last_update_date,
last_updated_by,
po_header_id,
po_line_id,
last_update_login,
creation_date,
created_by,
quantity,
quantity_received,
quantity_accepted,
quantity_rejected,
quantity_billed,
quantity_cancelled,
unit_meas_lookup_code,
po_release_id,
ship_to_location_id,
ship_via_lookup_code,
need_by_date,
promised_date,
last_accept_date,
price_override,
encumbered_flag,
encumbered_date,
unencumbered_quantity,
fob_lookup_code,
freight_terms_lookup_code,
taxable_flag,
tax_name,
estimated_tax_amount,
from_header_id,
from_line_id,
from_line_location_id,
start_date,
end_date,
lead_time,
lead_time_unit,
price_discount,
terms_id,
approved_flag,
approved_date,
closed_flag,
cancel_flag,
cancelled_by,
cancel_date,
cancel_reason,
firm_status_lookup_code,
firm_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
unit_of_measure_class,
encumber_now,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
inspection_required_flag,
receipt_required_flag,
qty_rcv_tolerance,
qty_rcv_exception_code,
enforce_ship_to_location_code,
allow_substitute_receipts_flag,
days_early_receipt_allowed,
days_late_receipt_allowed,
receipt_days_exception_code,
invoice_close_tolerance,
receive_close_tolerance,
ship_to_organization_id,
shipment_num,
source_shipment_id,
shipment_type,
closed_code,
request_id,
program_application_id,
program_id,
program_update_date,
ussgl_transaction_code,
government_context,
receiving_routing_id,
accrue_on_receipt_flag,
closed_reason,
closed_date,
closed_by,
org_id,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
quantity_shipped,
country_of_origin_code,
tax_user_override_flag,
match_option,
tax_code_id,
calculate_tax_flag,
change_promised_date_reason
)
SELECT line_location_id,
last_update_date,
last_updated_by,
po_header_id,
po_line_id,
last_update_login,
creation_date,
created_by,
quantity,
quantity_received,
quantity_accepted,
quantity_rejected,
quantity_billed,
quantity_cancelled,
unit_meas_lookup_code,
po_release_id,
ship_to_location_id,
ship_via_lookup_code,
need_by_date,
promised_date,
last_accept_date,
price_override,
encumbered_flag,
encumbered_date,
unencumbered_quantity,
fob_lookup_code,
freight_terms_lookup_code,
taxable_flag,
tax_name,
estimated_tax_amount,
from_header_id,
from_line_id,
from_line_location_id,
start_date,
end_date,
lead_time,
lead_time_unit,
price_discount,
terms_id,
approved_flag,
approved_date,
closed_flag,
cancel_flag,
cancelled_by,
cancel_date,
cancel_reason,
firm_status_lookup_code,
firm_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
unit_of_measure_class,
encumber_now,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
inspection_required_flag,
receipt_required_flag,
qty_rcv_tolerance,
qty_rcv_exception_code,
enforce_ship_to_location_code,
allow_substitute_receipts_flag,
days_early_receipt_allowed,
days_late_receipt_allowed,
receipt_days_exception_code,
invoice_close_tolerance,
receive_close_tolerance,
ship_to_organization_id,
shipment_num,
source_shipment_id,
shipment_type,
closed_code,
request_id,
program_application_id,
program_id,
program_update_date,
ussgl_transaction_code,
government_context,
receiving_routing_id,
accrue_on_receipt_flag,
closed_reason,
closed_date,
closed_by,
org_id,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
quantity_shipped,
country_of_origin_code,
tax_user_override_flag,
match_option,
tax_code_id,
calculate_tax_flag,
change_promised_date_reason
FROM po_line_locations PLLA
WHERE PLLA.po_header_id IN
( SELECT po_header_id
FROM po_headers PHA
WHERE PHA.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
)
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into PO Line Loc ALL Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
INSERT
INTO igc_cc_arc_po_distribution_all
(po_distribution_id,
last_update_date,
last_updated_by,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
code_combination_id,
quantity_ordered,
last_update_login,
creation_date,
created_by,
po_release_id,
quantity_delivered,
quantity_billed,
quantity_cancelled,
req_header_reference_num,
req_line_reference_num,
req_distribution_id,
deliver_to_location_id,
deliver_to_person_id,
rate_date,
rate,
amount_billed,
accrued_flag,
encumbered_flag,
encumbered_amount,
unencumbered_quantity,
unencumbered_amount,
failed_funds_lookup_code,
gl_encumbered_date,
gl_encumbered_period_name,
gl_cancelled_date,
destination_type_code,
destination_organization_id,
destination_subinventory,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
wip_entity_id,
wip_operation_seq_num,
wip_resource_seq_num,
wip_repetitive_schedule_id,
wip_line_id,
bom_resource_id,
budget_account_id,
accrual_account_id,
variance_account_id,
prevent_encumbrance_flag,
ussgl_transaction_code,
government_context,
destination_context,
distribution_num,
source_distribution_id,
request_id,
program_application_id,
program_id,
program_update_date,
project_id,
task_id,
expenditure_type,
project_accounting_context,
expenditure_organization_id,
gl_closed_date,
accrue_on_receipt_flag,
expenditure_item_date,
org_id,
kanban_card_id,
award_id,
mrc_rate_date,
mrc_rate,
mrc_encumbered_amount,
mrc_unencumbered_amount,
end_item_unit_number,
tax_recovery_override_flag,
recoverable_tax,
nonrecoverable_tax,
recovery_rate
)
SELECT po_distribution_id,
last_update_date,
last_updated_by,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
code_combination_id,
quantity_ordered,
last_update_login,
creation_date,
created_by,
po_release_id,
quantity_delivered,
quantity_billed,
quantity_cancelled,
req_header_reference_num,
req_line_reference_num,
req_distribution_id,
deliver_to_location_id,
deliver_to_person_id,
rate_date,
rate,
amount_billed,
accrued_flag,
encumbered_flag,
encumbered_amount,
unencumbered_quantity,
unencumbered_amount,
failed_funds_lookup_code,
gl_encumbered_date,
gl_encumbered_period_name,
gl_cancelled_date,
destination_type_code,
destination_organization_id,
destination_subinventory,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
wip_entity_id,
wip_operation_seq_num,
wip_resource_seq_num,
wip_repetitive_schedule_id,
wip_line_id,
bom_resource_id,
budget_account_id,
accrual_account_id,
variance_account_id,
prevent_encumbrance_flag,
ussgl_transaction_code,
government_context,
destination_context,
distribution_num,
source_distribution_id,
request_id,
program_application_id,
program_id,
program_update_date,
project_id,
task_id,
expenditure_type,
project_accounting_context,
expenditure_organization_id,
gl_closed_date,
accrue_on_receipt_flag,
expenditure_item_date,
org_id,
kanban_card_id,
award_id,
mrc_rate_date,
mrc_rate,
mrc_encumbered_amount,
mrc_unencumbered_amount,
end_item_unit_number,
tax_recovery_override_flag,
recoverable_tax,
nonrecoverable_tax,
recovery_rate
FROM po_distributions PDA
WHERE PDA.po_header_id IN
( SELECT po_header_id
FROM po_headers PHA
WHERE PHA.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
)
);
g_debug_msg := ' Inserted ' || SQL%ROWCOUNT || ' number rows into PO Dist ALL Table for ' ||
' Last Activity Date : ' || g_last_activity_date;
SELECT count(*)
FROM igc_arc_pur_candidates;
DELETE
FROM igc_arc_pur_candidates;
INSERT
INTO igc_arc_pur_candidates
(cc_header_id,
cc_num,
cc_acct_line_id,
cc_det_pf_line_id,
last_activity_date
)
SELECT ICCCHV.cc_hd_id,
ICCCHV.cc_num_val,
ICCCHV.cc_act_id,
ICCCHV.cc_pf_id,
ICCCHV.max_dt
FROM igc_cc_closed_canc_hdrs_v ICCCHV,
igc_cc_headers_all ICH
WHERE ICH.cc_header_id = ICCCHV.cc_hd_id
AND ICH.set_of_books_id = g_sob_id
AND ICH.org_id = g_org_id
AND trunc (ICCCHV.max_dt) <= trunc (g_last_activity_date)
AND ICH.cc_header_id NOT IN
( SELECT ICH1.cc_header_id
FROM igc_cc_headers ICH1,
po_headers_all PHA1,
po_distributions_all PDA,
ap_invoice_distributions_all AIDA,
ap_invoices_all AIA1
WHERE /*ICH1.set_of_books_id = g_sob_id
AND ICH1.org_id = g_org_id
AND --Commented during MOAC uptake */
PHA1.segment1 = ICH1.cc_num
AND PDA.po_header_id = PHA1.po_header_id
AND AIDA.po_distribution_id = PDA.po_distribution_id
AND AIA1.invoice_id = AIDA.invoice_id
AND AIA1.payment_status_flag = 'N'
AND AIA1.cancelled_date IS NULL
);
g_debug_msg := ' No Candidates Found to be inserted into table......';
DELETE
FROM igc_arc_pur_candidates
WHERE cc_header_id IN
( SELECT parent_header_id
FROM igc_cc_headers
WHERE parent_header_id IS NOT NULL
AND cc_header_id NOT IN
( SELECT cc_header_id
FROM igc_arc_pur_candidates
)
);
DELETE
FROM igc_arc_pur_candidates
WHERE cc_header_id IN
( SELECT cc_header_id
FROM igc_cc_headers
WHERE parent_header_id IS NOT NULL
AND parent_header_id NOT IN
( SELECT cc_header_id
FROM igc_arc_pur_candidates
)
);
g_debug_msg := ' No Candidates Found to be inserted into table......';
SELECT application_id
INTO l_igc_application_id
FROM fnd_application
WHERE application_short_name = 'IGC';
DELETE
FROM igc_cc_arc_mc_headers CMH
WHERE CMH.cc_header_id IN
( SELECT ICV.cc_header_id
FROM igc_arc_pur_candidates ICV
);
DELETE
FROM igc_cc_arc_mc_acct_lines CML
WHERE CML.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR CML.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
);
DELETE
FROM igc_cc_arc_mc_det_pf CMP
WHERE CMP.cc_det_pf_line_id IN
( SELECT DPFH.cc_det_pf_line_id
FROM igc_cc_det_pf_history DPFH
WHERE DPFH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR DPFH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
)
)
OR CMP.cc_det_pf_line_id IN
( SELECT DPF.cc_det_pf_line_id
FROM igc_cc_det_pf DPF
WHERE DPF.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR DPF.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
)
);
DELETE
FROM igc_cc_arc_mc_header_hist CMHH
WHERE CMHH.cc_header_id IN
( SELECT ICV.cc_header_id
FROM igc_arc_pur_candidates ICV
);
DELETE
FROM igc_cc_arc_mc_acct_line_hist CMLH
WHERE CMLH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR CMLH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
);
DELETE
FROM igc_cc_arc_mc_det_pf_hist CMPH
WHERE CMPH.cc_det_pf_line_id IN
( SELECT DPFH.cc_det_pf_line_id
FROM igc_cc_det_pf_history DPFH
WHERE DPFH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR DPFH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
)
)
OR CMPH.cc_det_pf_line_id IN
( SELECT DPF.cc_det_pf_line_id
FROM igc_cc_det_pf DPF
WHERE DPF.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR DPF.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
)
);
DELETE
FROM igc_cc_arc_headers_all CMH
WHERE CMH.cc_header_id IN
( SELECT ICV.cc_header_id
FROM igc_arc_pur_candidates ICV
);
DELETE
FROM igc_cc_arc_header_hist_all CAHH
WHERE CAHH.cc_header_id IN
( SELECT ICV.cc_header_id
FROM igc_arc_pur_candidates ICV
);
DELETE
FROM igc_cc_arc_acct_lines CML
WHERE CML.cc_header_id IN
( SELECT ICV.cc_header_id
FROM igc_arc_pur_candidates ICV
);
DELETE
FROM igc_cc_arc_acct_line_hist CML
WHERE CML.cc_header_id IN
( SELECT ICV.cc_header_id
FROM igc_arc_pur_candidates ICV
);
DELETE
FROM igc_cc_arc_det_pf CDP
WHERE CDP.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR CDP.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV2.cc_header_id
FROM igc_arc_pur_candidates ICV2
)
);
DELETE
FROM igc_cc_arc_det_pf_hist CDPH
WHERE CDPH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
)
OR CDPH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id IN
( SELECT ICV1.cc_header_id
FROM igc_arc_pur_candidates ICV1
)
);
DELETE
FROM igc_cc_arc_actions CAA
WHERE CAA.cc_header_id IN
( SELECT ICV.cc_header_id
FROM igc_arc_pur_candidates ICV
);
DELETE
FROM igc_cc_arc_po_headers IPHA
WHERE IPHA.po_header_id IN
( SELECT PHA1.po_header_id
FROM po_headers_all PHA1
WHERE PHA1.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
)
);
DELETE
FROM igc_cc_arc_po_lines IPLA
WHERE IPLA.po_header_id IN
( SELECT PHA.po_header_id
FROM po_headers_all PHA
WHERE PHA.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
)
);
DELETE
FROM igc_cc_arc_po_line_loc IPLLA
WHERE IPLLA.po_header_id IN
( SELECT PHA.po_header_id
FROM po_headers_all PHA
WHERE PHA.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
)
);
DELETE
FROM igc_cc_arc_po_distribution IPDA
WHERE IPDA.po_header_id IN
( SELECT PHA.po_header_id
FROM po_headers_all PHA
WHERE PHA.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
)
);
l_update_by igc_cc_archive_history.last_updated_by%TYPE;
SELECT cc_header_id,
set_of_books_id,
org_id,
parent_header_id,
cc_num,
cc_type,
num_acct_lines_arc,
num_det_pf_lines_arc,
num_mc_acct_lines_arc,
num_mc_det_pf_lines_arc,
archive_date,
last_updated_by,
archive_done_flag,
user_req_last_activity_date,
created_by,
creation_date
FROM IGC_CC_ARCHIVE_HISTORY CAH
WHERE CAH.cc_header_id = p_cc_header_id
AND CAH.org_id = g_org_id
AND CAH.set_of_books_id = g_sob_id;
l_update_by,
l_archive_done,
l_last_activity_date,
l_created_by,
l_created_date;
p_History_Rec.last_update_date := SYSDATE;
p_History_Rec.last_updated_by := g_update_by;
p_History_Rec.last_update_login := g_update_by;
p_History_Rec.archived_by := g_update_by;
p_History_Rec.archived_by := l_update_by;
p_History_Rec.purged_by := g_update_by;
p_History_Rec.archived_by := g_update_by;
p_History_Rec.created_by := g_update_by;
PROCEDURE Insert_Archive_History (
x_Return_Status OUT NOCOPY VARCHAR2
) IS
-- --------------------------------------------------------------------
-- Declare local variables to be used in this procedure.
-- --------------------------------------------------------------------
l_return_status VARCHAR2(1);
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Archive_History';
SELECT distinct (cc_header_id),
cc_num,
last_activity_date
FROM igc_arc_pur_candidates;
SELECT cc_header_id,
cc_num,
cc_type,
parent_header_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_header_id;
SELECT count(*)
FROM igc_cc_arc_acct_lines
WHERE cc_header_id = l_cc_header_id;
SELECT count(*)
FROM igc_cc_arc_det_pf ICADP
WHERE ICADP.cc_acct_line_id IN
( SELECT ICAAL.cc_acct_line_id
FROM igc_cc_arc_acct_lines ICAAL
WHERE ICAAL.cc_header_id = l_cc_header_id
);
SELECT count(*)
FROM igc_cc_arc_mc_acct_lines ICAMAL
WHERE ICAMAL.cc_acct_line_id IN
( SELECT ICAAL.cc_acct_line_id
FROM igc_cc_arc_acct_lines ICAAL
WHERE ICAAL.cc_header_id = l_cc_header_id
);
SELECT count(*)
FROM igc_cc_arc_mc_det_pf ICAMDP
WHERE ICAMDP.cc_det_pf_line_id IN
( SELECT ICADP.cc_det_pf_line_id
FROM igc_cc_arc_det_pf ICADP
WHERE ICADP.cc_acct_line_id IN
( SELECT ICAAL.cc_acct_line_id
FROM igc_cc_arc_acct_lines ICAAL
WHERE ICAAL.cc_header_id = l_cc_header_id
)
);
l_full_path := g_path || 'Insert_Archive_History';
g_debug_msg := ' Beginning Inserting Archive History Records for Last Activity Date : ' ||
g_last_activity_date;
g_debug_msg := ' Inserting Archive History Record for CC Header ID : ' ||
l_cc_header_id;
Update_History (p_History_Rec => l_history_rec,
x_Return_Status => l_return_status
);
g_debug_msg := 'No Archive Candidates to insert into history table.';
END Insert_Archive_History;
SELECT cc_header_id
FROM igc_arc_pur_candidates
FOR UPDATE NOWAIT;
g_debug_msg := ' Attempt delete records in MRC tables. Last Activity Date : ' ||
g_last_activity_date;
g_debug_msg := ' Attempt delete records in NON MRC tables. Last Activity Date : ' ||
g_last_activity_date;
SELECT num_mc_acct_lines_arc,
num_mc_det_pf_lines_arc
FROM igc_cc_archive_history
WHERE cc_header_id = l_cc_header_id
AND set_of_books_id = g_sob_id
AND org_id = g_org_id
AND archive_done_flag = 'Y';
SELECT distinct (ICAP.cc_header_id)
FROM igc_arc_pur_candidates ICAP;
DELETE
FROM igc_cc_mc_det_pf_history DPH
WHERE DPH.cc_det_pf_line_id IN
( SELECT DPFH.cc_det_pf_line_id
FROM igc_cc_det_pf_history DPFH
WHERE DPFH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id = l_cc_header_id
)
OR DPFH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id = l_cc_header_id
)
)
OR DPH.cc_det_pf_line_id IN
( SELECT DPF.cc_det_pf_line_id
FROM igc_cc_det_pf DPF
WHERE DPF.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id = l_cc_header_id
)
OR DPF.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id = l_cc_header_id
)
);
g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_DET_PF_HISTORY for CC Header ID : ' ||
l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_mc_det_pf DP
WHERE DP.cc_det_pf_line_id IN
( SELECT DPFH.cc_det_pf_line_id
FROM igc_cc_det_pf_history DPFH
WHERE DPFH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id = l_cc_header_id
)
OR DPFH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id = l_cc_header_id
)
)
OR DP.cc_det_pf_line_id IN
( SELECT DPF.cc_det_pf_line_id
FROM igc_cc_det_pf DPF
WHERE DPF.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id = l_cc_header_id
)
OR DPF.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id = l_cc_header_id
)
);
g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_DET_PF for CC Header ID : ' ||
l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_mc_acct_line_history CALH
WHERE CALH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id = l_cc_header_id
)
OR CALH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id = l_cc_header_id
);
g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_ACCT_LINE_HISTORY CC Header ID : ' ||
l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_mc_acct_lines CAL
WHERE CAL.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id = l_cc_header_id
)
OR CAL.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id = l_cc_header_id
);
g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_ACCT_LINES for CC Header ID : ' ||
l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_mc_header_history
WHERE cc_header_id = l_cc_header_id;
g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_HEADER_HISTORY for CC Header ID : ' ||
l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_mc_headers
WHERE cc_header_id = l_cc_header_id;
g_debug_msg := 'Number Rows Deleted from IGC_CC_MC_HEADERS for CC Header ID : ' ||
l_cc_header_id || ' is : ' || to_char(SQL%ROWCOUNT);
SELECT num_acct_lines_arc,
num_det_pf_lines_arc
FROM igc_cc_archive_history
WHERE cc_header_id = l_cc_header_id
/* AND set_of_books_id = g_sob_id
AND org_id = g_org_id --Commented for MOAC uptake */
AND archive_done_flag = 'Y';
SELECT distinct (ICAP.cc_header_id)
FROM igc_arc_pur_candidates ICAP;
DELETE
FROM igc_cc_actions
WHERE cc_header_id = l_cc_header_id;
g_debug_msg := 'Number Rows Deleted from IGC_CC_ACTIONS for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
DELETE
FROM po_distributions_all PDA
WHERE PDA.po_header_id IN
( SELECT PHA.po_header_id
FROM po_headers_all PHA
WHERE PHA.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
WHERE ICV.cc_header_id = l_cc_header_id
)
);
g_debug_msg := 'Number Rows Deleted from PO_DISTRIBUTIONS_ALL for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
DELETE
FROM po_line_locations_all PLLA
WHERE PLLA.po_header_id IN
( SELECT PHA.po_header_id
FROM po_headers_all PHA
WHERE PHA.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
WHERE ICV.cc_header_id = l_cc_header_id
)
);
g_debug_msg := 'Number Rows Deleted from PO_LINE_LOCATIONS_ALL for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
DELETE
FROM po_lines_all PLA
WHERE PLA.po_header_id IN
( SELECT PHA.po_header_id
FROM po_headers_all PHA
WHERE PHA.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
WHERE ICV.cc_header_id = l_cc_header_id
)
);
g_debug_msg := 'Number Rows Deleted from PO_LINES_ALL for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
DELETE
FROM po_headers_all PHA
WHERE PHA.po_header_id IN
( SELECT PHA1.po_header_id
FROM po_headers_all PHA1
WHERE PHA1.segment1 IN
( SELECT ICV.cc_num
FROM igc_arc_pur_candidates ICV
WHERE ICV.cc_header_id = l_cc_header_id
)
);
g_debug_msg := 'Number Rows Deleted from PO_HEADERS_ALL for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_det_pf_history CDPH
WHERE CDPH.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id = l_cc_header_id
)
OR CDPH.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id = l_cc_header_id
);
g_debug_msg := 'Number Rows Deleted from IGC_CC_DET_PF_HISTORY for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_det_pf CDP
WHERE CDP.cc_acct_line_id IN
( SELECT ACLH.cc_acct_line_id
FROM igc_cc_acct_line_history ACLH
WHERE ACLH.cc_header_id = l_cc_header_id
)
OR CDP.cc_acct_line_id IN
( SELECT ACL.cc_acct_line_id
FROM igc_cc_acct_lines ACL
WHERE ACL.cc_header_id = l_cc_header_id
);
g_debug_msg := 'Number Rows Deleted from IGC_CC_DET_PF for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_acct_line_history ALH
WHERE ALH.cc_header_id = l_cc_header_id;
g_debug_msg := 'Number Rows Deleted from IGC_CC_ACCT_LINE_HISTORY for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_acct_lines AL
WHERE AL.cc_header_id = l_cc_header_id;
g_debug_msg := 'Number Rows Deleted from IGC_CC_ACCT_LINES for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_interface CI
WHERE CI.cc_header_id = l_cc_header_id
AND CI.set_of_books_id = g_sob_id;
g_debug_msg := 'Number Rows Deleted from IGC_CC_INTERFACE for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_header_history CHH
WHERE CHH.cc_header_id = l_cc_header_id
AND CHH.set_of_books_id = g_sob_id;
g_debug_msg := 'Number Rows Deleted from IGC_CC_HEADER_HISTORY for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || g_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
DELETE
FROM igc_cc_headers CH
WHERE CH.cc_header_id = l_cc_header_id
AND CH.set_of_books_id = g_sob_id;
g_debug_msg := 'Number Rows Deleted from IGC_CC_HEADERS for CC Header ID : ' ||
l_cc_header_id || ' SOB ID : ' || l_sob_id || ' is : ' ||
to_char(SQL%ROWCOUNT);
l_history_rec.purged_by := g_update_by;
Update_History (p_History_Rec => l_history_rec,
x_Return_Status => l_return_status
);
PROCEDURE Update_History
(
p_History_Rec IN igc_cc_archive_history%ROWTYPE, -- History Record
x_Return_Status OUT NOCOPY VARCHAR2 -- Status of procedure
) IS
-- --------------------------------------------------------------------
-- Declare cursors to be used in this procedure.
-- --------------------------------------------------------------------
CURSOR c_archive_history IS
SELECT CAH.cc_header_id
FROM igc_cc_archive_history CAH
WHERE CAH.cc_header_id = p_History_Rec.cc_header_id
AND CAH.cc_num = p_History_Rec.cc_num
AND CAH.cc_type = p_History_Rec.cc_type;
l_api_name CONSTANT VARCHAR2(30) := 'Update_History';
l_full_path := g_path || 'Update_History';
g_debug_msg := ' Calling Update History for CC Header ID : ' ||
p_History_Rec.cc_header_id;
g_debug_msg := ' Inserting Archive History Record for CC Header ID : ' ||
p_History_Rec.cc_header_id ||
' Set Of Books ID : ' || p_History_Rec.set_of_books_id;
INSERT
INTO IGC_CC_ARCHIVE_HISTORY_ALL
( cc_header_id,
set_of_books_id,
org_id,
parent_header_id,
cc_num,
cc_type,
archive_date,
archived_by,
archive_done_flag,
purge_date,
purged_by,
purge_done_flag,
num_acct_lines_arc,
num_det_pf_lines_arc,
num_mc_acct_lines_arc,
num_mc_det_pf_lines_arc,
last_cc_activity_date,
user_req_last_activity_date,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date
)
VALUES
( p_History_Rec.cc_header_id,
p_History_Rec.set_of_books_id,
p_History_Rec.org_id,
NVL (p_History_Rec.parent_header_id, 0),
p_History_Rec.cc_num,
p_History_Rec.cc_type,
p_History_Rec.archive_date,
p_History_Rec.archived_by,
p_History_Rec.archive_done_flag,
p_History_Rec.purge_date,
p_History_Rec.purged_by,
p_History_Rec.purge_done_flag,
NVL (p_History_Rec.num_acct_lines_arc, 0),
NVL (p_History_Rec.num_det_pf_lines_arc, 0),
NVL (p_History_Rec.num_mc_acct_lines_arc, 0),
NVL (p_History_Rec.num_mc_det_pf_lines_arc, 0),
p_History_Rec.last_cc_activity_date,
p_History_Rec.user_req_last_activity_date,
p_History_Rec.last_update_date,
p_History_Rec.last_updated_by,
p_History_Rec.last_update_login,
p_History_Rec.created_by,
p_History_Rec.creation_date
);
p_msgname => 'IGC_INSERT_ARC_PUR_HISTORY');
ELSE -- Update existing record
-- IF (IGC_MSGS_PKG.g_debug_mode) THEN
IF (g_debug_mode = 'Y') THEN
g_debug_msg := ' Updating Archive History Record for CC Header ID : ' ||
p_History_Rec.cc_header_id ||
' Set Of Books ID : ' || p_History_Rec.set_of_books_id;
UPDATE IGC_CC_ARCHIVE_HISTORY AH
SET last_update_date = p_History_Rec.last_update_date,
last_updated_by = p_History_Rec.last_updated_by,
purge_done_flag = p_History_Rec.purge_done_flag,
purge_date = p_History_Rec.purge_date,
purged_by = p_History_Rec.purged_by,
archive_done_flag = p_History_Rec.archive_done_flag,
archive_date = p_History_Rec.archive_date,
archived_by = p_History_Rec.archived_by,
num_acct_lines_arc = NVL (p_History_Rec.num_acct_lines_arc, 0),
num_det_pf_lines_arc = NVL (p_History_Rec.num_det_pf_lines_arc, 0),
num_mc_acct_lines_arc = NVL (p_History_Rec.num_mc_acct_lines_arc, 0),
num_mc_det_pf_lines_arc = NVL (p_History_Rec.num_mc_det_pf_lines_arc, 0)
WHERE cc_header_id = p_History_Rec.cc_header_id
/* AND org_id = p_History_Rec.org_id
AND set_of_books_id = p_History_Rec.set_of_books_id
--Commented for MOAC uptake */
AND NVL(parent_header_id, 0) = NVL(p_History_Rec.parent_header_id, 0)
AND cc_num = p_History_Rec.cc_num
AND cc_type = p_History_Rec.cc_type;
p_msgname => 'IGC_UPDATE_ARC_PUR_HISTORY');
END Update_History;
SELECT ICCH.cc_header_id
FROM igc_cc_headers ICCH
WHERE ICCH.set_of_books_id = g_sob_id
AND ICCH.org_id = g_org_id;