[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| the CBC Funds Checker process and inserted into the table |
| IGC_CBC_JE_LINES. |
| |
| If there is to be any changes inside of this procedure then |
| there needs to be analysis performed on the effect it will have|
| on the Funds Checker process. |
| |
| Parameters : |
| |
| Standard header params for Public Procedures. |
| |
| p_api_version Version number for API to run |
| p_init_msg_list Message stack to be initialized flag |
| p_commit Is work to be commited here flag |
| p_validation_level Validation Level to be performed |
| p_return_status Status returned from Procedure |
| p_msg_count Number of messages on stack returned |
| p_msg_data Message text information returned |
| |
| Parameters for Procedure to process properly. |
| |
| p_validation_type Type of Validation FC (Funds), LC (Legacy) |
| p_ccid Code Combination ID From GL tables |
| p_transaction_date Date transaction to compare period start / end |
| p_det_sum_value Detail (D) or Summary (S) transaction |
| p_set_of_books_id Set Of Books being processed |
| p_actual_flag Actual Flag for Encumbrance or Budget. |
| p_result_code Result Code mapping for status update to user |
| |
+=======================================================================*/
PROCEDURE Validate_CCID
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_validation_type IN VARCHAR2,
p_ccid IN igc_cbc_je_lines.code_combination_id%TYPE, -- Contract ID
p_effective_date IN igc_cbc_je_lines.effective_date%TYPE, -- Transaction Date
p_det_sum_value IN igc_cbc_je_lines.detail_summary_code%TYPE,
p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
p_actual_flag IN VARCHAR2,
p_result_code OUT NOCOPY VARCHAR2
) IS
-- --------------------------------------------------------------------
-- Declare the cursors to be used during this function run.
-- --------------------------------------------------------------------
CURSOR c_validate_ccid_values IS
SELECT GCC.detail_budgeting_allowed_flag,
GCC.detail_posting_allowed_flag,
GCC.enabled_flag,
GCC.start_date_active,
GCC.end_date_active
FROM gl_code_combinations GCC,
gl_sets_of_books GSB
WHERE GCC.code_combination_id = p_ccid
AND GSB.set_of_books_id = p_set_of_books_id
AND GCC.chart_of_accounts_id = GSB.chart_of_accounts_id;
| entered into the CBC Funds Checker process and inserted into |
| the table IGC_CBC_JE_LINES. |
| |
| If there is to be any changes inside of this procedure then |
| there needs to be analysis performed on the effect it will have|
| on the Funds Checker process. |
| |
| Parameters : |
| |
| Standard header params for Public Procedures. |
| |
| p_api_version Version number for API to run |
| p_init_msg_list Message stack to be initialized flag |
| p_commit Is work to be commited here flag |
| p_validation_level Validation Level to be performed |
| p_return_status Status returned from Procedure |
| p_msg_count Number of messages on stack returned |
| p_msg_data Message text information returned |
| |
| Parameters for Procedure to process properly. |
| |
| p_efc_enabled Enhanced Funds Checker enabled flag. |
| p_set_of_books_id GL Set Of books ID being processed |
| p_actual_flag Actual Flag for Encumbrance or Budget. |
| p_ccid GL Code Combination ID |
| p_det_sum_value Detail (D) or Summary (S) transaction |
| p_currency_code Currency Code that transaction is for |
| p_effective_date Transaction date for period range |
| p_budget_ver_id Funding Budget Version ID if Budget CCID |
| p_out_budget_ver_id Funding Budget Version ID if available for CCID|
| p_amount_type Amount type in GL for CCID |
| p_funds_level_code What level of Funds Check required |
| |
+=======================================================================*/
PROCEDURE Validate_Get_CCID_Budget_Info
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_efc_enabled IN VARCHAR2,
p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
p_actual_flag IN VARCHAR2,
p_ccid IN igc_cbc_je_lines.code_combination_id%TYPE,
p_det_sum_value IN igc_cbc_je_lines.detail_summary_code%TYPE,
p_currency_code IN igc_cbc_je_lines.currency_code%TYPE,
p_effective_date IN igc_cbc_je_lines.effective_date%TYPE, -- Transaction Date
p_budget_ver_id IN igc_cbc_je_lines.budget_version_id%TYPE,
p_out_budget_ver_id OUT NOCOPY igc_cbc_je_lines.budget_version_id%TYPE,
p_amount_type OUT NOCOPY igc_cbc_je_lines.amount_type%TYPE,
p_funds_level_code OUT NOCOPY igc_cbc_je_lines.funds_check_level_code%TYPE
) IS
-- -------------------------------------------------------------------------
-- Declare local variables used within fuction
-- -------------------------------------------------------------------------
l_budget_ver_id igc_cbc_je_lines.budget_version_id%TYPE;
SELECT GST.amount_type,
GST.funds_check_level_code,
GST.cbc_override,
ST.funding_budget_version_id
FROM igc_cbc_summary_templates_v GST,
psa_efc_summary_budgets ST,
gl_budget_versions BVR,
gl_budgets BUD,
gl_period_statuses FPER,
gl_period_statuses LPER
WHERE GST.template_id IN
( SELECT template_id
FROM gl_account_hierarchies
WHERE set_of_books_id = :1
AND summary_code_combination_id = :2
)
AND GST.template_id = ST.template_id
AND GST.set_of_books_id = FPER.set_of_books_id
AND ST.funding_budget_version_id = BVR.budget_version_id
AND BVR.budget_name = BUD.budget_name
AND FPER.set_of_books_id = :3
AND LPER.set_of_books_id = :4
AND BUD.first_valid_period_name = FPER.period_name
AND BUD.last_valid_period_name = LPER.period_name
AND FPER.application_id = :5
AND LPER.application_id = :6
AND :7 BETWEEN FPER.start_date AND LPER.end_date
';
SELECT GBA.amount_type,
GBA.funds_check_level_code,
BAR.cbc_override,
GBA.funding_budget_version_id
FROM gl_budget_assignments GBA,
igc_cbc_ba_ranges BAR,
gl_budget_assignment_ranges asg,
gl_budget_versions bvr,
gl_budgets bud,
gl_period_statuses fper,
gl_period_statuses lper
/*R12 Uptake - Commented for compilation Bug No 6341012*/
-- WHERE GBA.set_of_books_id = p_set_of_books_id
WHERE GBA.ledger_id = p_set_of_books_id
/*R12 Uptake - Commented for compilation Bug No 6341012*/
-- AND BAR.set_of_books_id(+) = GBA.set_of_books_id
AND BAR.set_of_books_id(+) = GBA.ledger_id
AND GBA.code_combination_id = p_ccid
AND GBA.currency_code = p_currency_code
AND GBA.range_id = BAR.cbc_range_id(+)
AND GBA.range_id = asg.range_id
AND asg.funding_budget_version_id=bvr.budget_version_id
AND bvr.budget_name=bud.budget_name
AND fper.set_of_books_id = p_set_of_books_id
AND lper.set_of_books_id = p_set_of_books_id
AND bud.first_valid_period_name = fper.period_name
AND bud.last_valid_period_name = lper.period_name
AND fper.application_id = l_gl_application_id
AND lper.application_id = l_gl_application_id
AND p_effective_date BETWEEN fper.start_date AND lper.end_date
;
SELECT GST.amount_type,
GST.funds_check_level_code,
GST.cbc_override,
GST.funding_budget_version_id
FROM igc_cbc_summary_templates_v GST,
gl_account_hierarchies GAH
--R12 Uptake - Commented for compilation
-- WHERE GAH.set_of_books_id = p_set_of_books_id
WHERE GAH.ledger_id = p_set_of_books_id
AND GAH.summary_code_combination_id = p_ccid
AND GST.template_id = GAH.template_id
--R12 Uptake - Commented for compilation
-- AND GST.set_of_books_id = GAH.set_of_books_id;
SELECT application_id
INTO l_gl_application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL';
| entered into the CBC Funds Checker process and inserted into |
| the table IGC_CBC_JE_LINES. |
| |
| If there is to be any changes inside of this procedure then |
| there needs to be analysis performed on the effect it will have|
| on the Funds Checker process. |
| |
| Parameters : |
| |
| Standard header params for Public Procedures. |
| |
| p_api_version Version number for API to run |
| p_init_msg_list Message stack to be initialized flag |
| p_commit Is work to be commited here flag |
| p_validation_level Validation Level to be performed |
| p_return_status Status returned from Procedure |
| p_msg_count Number of messages on stack returned |
| p_msg_data Message text information returned |
| |
| Parameters for Procedure to process properly. |
| |
| p_sob_id GL Set of Books ID to be processed |
| p_effect_date Transaction Date |
| p_check_type Type of check Funds (FC) or Legacy (LC) |
| p_period_name Period name for CCID if found for Check type |
| p_period_set_name Period Set Name for CCID if found |
| p_quarter_num Quarter number for CCID if found |
| p_period_num Period Number for CCID if found |
| p_period_year Period Year for CCID if found |
| p_result_status Result Code for updating line status |
| |
+=======================================================================*/
PROCEDURE Validate_Get_CCID_Period_Name
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_sob_id IN gl_sets_of_books.set_of_books_id%TYPE,
p_effect_date IN igc_cbc_je_lines.effective_date%TYPE,
p_check_type IN VARCHAR2,
p_period_name OUT NOCOPY igc_cbc_je_lines.period_name%TYPE,
p_period_set_name OUT NOCOPY igc_cbc_je_lines.period_set_name%TYPE,
p_quarter_num OUT NOCOPY igc_cbc_je_lines.quarter_num%TYPE,
p_period_num OUT NOCOPY igc_cbc_je_lines.period_num%TYPE,
p_period_year OUT NOCOPY igc_cbc_je_lines.period_year%TYPE,
p_result_status OUT NOCOPY VARCHAR2
) IS
-- -------------------------------------------------------------------------
-- Declare local variables used within fuction
-- -------------------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Get_CCID_Period_Name';
SELECT GPS.period_name,
GP.period_set_name,
GPS.period_num,
GPS.period_year,
GPS.quarter_num
FROM gl_period_statuses GPS,
gl_sets_of_books GP
WHERE GPS.set_of_books_id = p_sob_id
AND GPS.application_id = l_gl_application_id
AND GPS.adjustment_period_flag = 'N'
AND GP.set_of_books_id = GPS.set_of_books_id
-- AND to_date (p_effect_date)
AND p_effect_date BETWEEN GPS.start_date AND GPS.end_date
AND GPS.closing_status
IN ('O','F');
SELECT GPS.period_name,
GP.period_set_name,
GPS.period_num,
GPS.period_year,
GPS.quarter_num
FROM gl_period_statuses GPS,
gl_sets_of_books GP
WHERE GPS.set_of_books_id = p_sob_id
AND GPS.application_id = l_gl_application_id
AND GPS.adjustment_period_flag = 'N'
AND GP.set_of_books_id = GPS.set_of_books_id
AND GPS.closing_status = 'O'
-- AND to_date (p_effect_date)
AND p_effect_date BETWEEN GPS.start_date AND GPS.end_date;
SELECT application_id
INTO l_gl_application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL';
| entered into the CBC Funds Checker process and inserted into |
| the table IGC_CBC_JE_LINES. |
| |
| If there is to be any changes inside of this procedure then |
| there needs to be analysis performed on the effect it will have|
| on the Funds Checker process. |
| |
| Parameters : |
| |
| Standard header params for Public Procedures. |
| |
| p_api_version Version number for API to run |
| p_init_msg_list Message stack to be initialized flag |
| p_commit Is work to be commited here flag |
| p_validation_level Validation Level to be performed |
| p_return_status Status returned from Procedure |
| p_msg_count Number of messages on stack returned |
| p_msg_data Message text information returned |
| |
| Parameters for Procedure to process properly. |
| |
| p_sob_id GL Set Of Books being processed |
| p_efc_enabled Enhanced Funds Checker enabled Flag |
| |
+=======================================================================*/
PROCEDURE Validate_Check_EFC_Enabled
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_sob_id IN gl_sets_of_books.set_of_books_id%TYPE,
p_efc_enabled OUT NOCOPY VARCHAR2
) IS
-- --------------------------------------------------------------------
-- Declare the cursors to be used during this function run.
-- --------------------------------------------------------------------
CURSOR c_efc_table (p_schema VARCHAR2) IS
SELECT '1'
FROM all_tables
WHERE table_name = 'PSA_EFC_OPTIONS'
AND owner = p_schema;
'SELECT mult_funding_budgets_flag FROM psa_efc_options WHERE set_of_books_id = :1'
INTO l_enable
USING p_sob_id;
| entered into the CBC Funds Checker process and inserted into |
| the table IGC_CBC_JE_LINES. |
| |
| If there is to be any changes inside of this procedure then |
| there needs to be analysis performed on the effect it will have|
| on the Funds Checker process. |
| |
| Parameters : |
| |
| Standard header params for Public Procedures. |
| |
| p_api_version Version number for API to run |
| p_init_msg_list Message stack to be initialized flag |
| p_commit Is work to be commited here flag |
| p_validation_level Validation Level to be performed |
| p_return_status Status returned from Procedure |
| p_msg_count Number of messages on stack returned |
| p_msg_data Message text information returned |
| |
| Parameters for Procedure to process properly. |
| |
| p_sob_id GL Set Or Books ID being processed |
| p_cbc_enabled Commitment Budgetary Control enabled flag |
| p_cc_head_id Contract Commitment Header ID |
| p_actl_flag Actual Flag for GL processing |
| p_documt_type Contract Commitment Document Type |
| p_sum_line_num Summary Template Line Number |
| p_cbc_flag Is there CBC Lines present in table |
| p_sbc_flag Is there SBC Lines present in table |
| p_packet_id packet_id, if originated in Purchasing |
| |
+=======================================================================*/
PROCEDURE Validate_CC_Interface
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_sob_id IN gl_sets_of_books.set_of_books_id%TYPE,
p_cbc_enabled IN VARCHAR2,
p_cc_head_id IN igc_cbc_je_batches.cc_header_id%TYPE,
p_actl_flag IN VARCHAR2,
p_documt_type IN igc_cc_interface.document_type%TYPE,
-- p_sum_line_num OUT NOCOPY igc_cbc_je_lines.cbc_je_line_num%TYPE,
p_cbc_flag OUT NOCOPY VARCHAR2,
p_sbc_flag OUT NOCOPY VARCHAR2
-- p_packet_id IN NUMBER
) IS
-- --------------------------------------------------------------------
-- Declare the cursors to be used during this function run.
-- --------------------------------------------------------------------
CURSOR c_cbc_count IS --Check if CBC records in the interface table
SELECT count(*)
/*
,max(batch_line_num)
*/
FROM igc_cc_interface_v
WHERE budget_dest_flag ='C'
AND cc_header_id = p_cc_head_id
AND actual_flag = p_actl_flag
AND document_type = p_documt_type;
SELECT count(*)
FROM igc_cc_interface_v
WHERE budget_dest_flag = 'S'
AND cc_header_id = p_cc_head_id
AND actual_flag = p_actl_flag
AND document_type = p_documt_type;
SELECT count(DISTINCT set_of_books_id)
FROM igc_cc_interface_v
WHERE cc_header_id = p_cc_head_id
AND actual_flag = p_actl_flag
AND document_type = p_documt_type;
SELECT count(*)
FROM igc_cc_interface_v
WHERE cc_header_id = p_cc_head_id
AND actual_flag = p_actl_flag
AND document_type = p_documt_type
AND ( cbc_result_code IS NOT NULL
OR status_code IS NOT NULL ); */
SELECT count(*)
FROM igc_cc_interface_v
WHERE cc_header_id = p_cc_head_id
AND actual_flag = 'E'
AND document_type = p_documt_type
AND encumbrance_type_id IS NULL;
SELECT count(*),
max(batch_line_num)
FROM igc_cc_interface_v
WHERE budget_dest_flag ='C'
AND reference_6 = p_packet_id
AND actual_flag = p_actl_flag ;
SELECT count(*)
FROM igc_cc_interface_v
WHERE budget_dest_flag = 'S'
AND reference_6 = p_packet_id
AND actual_flag = p_actl_flag ;
SELECT count(*)
FROM igc_cc_interface_v
WHERE reference_6 = p_packet_id
AND actual_flag = 'E'
AND encumbrance_type_id IS NULL;