[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Subledger Update Process pkg
*
* CONTENTS
* PROCEDURE test_update ( ... )
*
* NOTES
* scheduled_on in control table is always sysdate since we are called
* at the appropriate time by conc.mgr.
*
* HISTORY
* 24-Dec-2002 Rajesh Seshadri - Created
* 14-Apr-2004 Dinesh Vadivel - Bug # 3196846
* Added Lot Cost Adjustment related changes TDD 13.13.5
* 30-OCT-2009 Vpedarla - Bug: 8978816
* modified the procedure insert_control_record. Since Order management
* entity is not getting executed in pre-processor wrapper
* 01-Feb-2013 Bug15954309 pmarada Enable parallel procesing in the wrapper
* based on number of process parameter
*
* TBD
* - messages using msg dict.
*
******************************************************************************/
G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
* update_process
*
* DESCRIPTION
* Wrapper to the subledger update concurrent program. Accepts the
* parameters to the subledger process, validates it, inserts the control
* record, then submits the subledger process as a child request. It puts
* itself in a paused state till the program completes and returns the
* status back to the ccm.
*
* INPUT PARAMETERS
* All parameters to the conc. request
*
* HISTORY
* 26-Dec-2002 Rajesh Seshadri
*
* 14-Apr-2004 Dinesh Vadivel Bug # 3196846 Lot Cost Adjsutment related changes TDD 13.13.5
* Now allowing the process to be submitted for CM source even if
* GL Cost Method is a Lot Cost Method.Also, skipping the validation of
* "revaluation parameter" for Lot Cost Method.
* 29-Jan-2013 pmarada, bug15954309, enable paralle processing enhancement. added new parameters
* num_of_process and post_txn_cmpt_cost.
*************************************************************************************************/
PROCEDURE update_process(
x_errbuf OUT NOCOPY VARCHAR2
, x_retcode OUT NOCOPY VARCHAR2
, p_legal_entity_id IN VARCHAR2
, p_ledger_id IN VARCHAR2
, p_cost_type_id IN VARCHAR2
, p_gl_fiscal_year IN VARCHAR2
, p_gl_period IN VARCHAR2
, p_test_posting IN VARCHAR2
, p_open_gl_date IN VARCHAR2
, p_posting_start_date IN VARCHAR2
, p_posting_end_date IN VARCHAR2
, p_post_if_no_cost IN VARCHAR2
, p_post_txn_cmpt_cost IN NUMBER
, p_process_category IN VARCHAR2
, p_num_of_process IN NUMBER
, p_crev_curr_calendar IN VARCHAR2
, p_crev_curr_period IN VARCHAR2
, p_crev_prev_cost_type_id IN VARCHAR2
, p_crev_prev_calendar IN VARCHAR2
, p_crev_prev_period IN VARCHAR2
, p_crev_gl_trans_date IN VARCHAR2
/* start invconv umoogala
p_post_cm IN VARCHAR2,
p_post_ic IN VARCHAR2,
p_post_om IN VARCHAR2,
p_post_pm IN VARCHAR2,
p_post_pur IN VARCHAR2
*/
) AS
l_closed_per_ind NUMBER(3) := 0;
SELECT NVL(mthd.lot_actual_cost,0)
FROM gl_plcy_mst plcy, cm_mthd_mst mthd
WHERE plcy.co_code = cp_co_code
;
l_procedure_name CONSTANT VARCHAR2(30) := 'UPDATE_PROCESS';
SELECT MIN(batch_id) from_batch_id,
MAX(batch_id) to_batch_id,
COUNT(*),
bucket_num
FROM (
SELECT batch_id,
NTILE(cp_num_req) OVER(ORDER BY batch_id) bucket_num
FROM (
SELECT bh.batch_id
FROM gme_batch_header bh
,mtl_material_transactions mmt
,gmf_organization_definitions god
WHERE mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id IN (1, 27, 31, 32)
AND mmt.opm_costed_flag IS NOT NULL
AND god.legal_entity_id = cp_le_id
AND bh.batch_id = mmt.transaction_source_id
AND mmt.organization_id = god.organization_id
AND mmt.transaction_date between cp_posting_start_date and cp_posting_end_date
UNION
SELECT bh.batch_id
FROM gme_batch_header bh
,gme_resource_txns rt
,gmf_organization_definitions god
WHERE rt.posted_ind = 0
AND rt.delete_mark = 0
AND rt.completed_ind = 1
AND god.legal_entity_id = cp_le_id
AND bh.batch_id = rt.doc_id
AND rt.organization_id = god.organization_id
AND rt.trans_date between cp_posting_start_date and cp_posting_end_date
UNION
SELECT bh.batch_id
FROM gme_batch_header bh,
gmf_organization_definitions god
WHERE bh.batch_close_date IS NOT NULL
AND bh.batch_status = 4
AND bh.gl_posted_ind = 0
AND nvl(bh.update_inventory_ind, 'N') = 'Y'
AND god.legal_entity_id = cp_le_id
AND bh.organization_id = god.organization_id
AND bh.batch_close_date between l_up_posting_start_date and l_up_posting_end_date
)
)
GROUP BY bucket_num
ORDER BY bucket_num;
SELECT reference_no, request_id, count(*) over()
FROM gl_subr_sta
WHERE legal_entity_id = cp_le_id
AND ledger_id = cp_ledger_id
AND cost_type_id = cp_cost_type_id
AND fiscal_year = cp_gl_fiscal_year
AND period = cp_gl_period
AND completion_ind = 0
AND stop_ind = 0
AND rownum = 1
AND (cp_post_pm = 1 AND post_pm = 1);
SELECT reference_no, request_id, count(*) over()
FROM gl_subr_sta
WHERE legal_entity_id = cp_le_id
AND ledger_id = cp_ledger_id
AND cost_type_id = cp_cost_type_id
AND fiscal_year = cp_gl_fiscal_year
AND period = cp_gl_period
AND completion_ind = 0
AND stop_ind = 0
AND rownum = 1
AND (
(cp_post_ic = 1 AND post_ic = 1) OR
(cp_post_om = 1 AND post_om = 1) OR
(cp_post_pur = 1 AND post_pur = 1)
)
AND (
(cp_posting_start_date between period_start_date and period_end_date) OR
(cp_posting_end_date between period_start_date and period_end_date)
)
;
SELECT le.organization_name, led.name,
mthd.cost_type, mthd.cost_mthd_code, lk.meaning,
mthd.default_lot_cost_type_id
INTO g_legal_entity_name, g_ledger_name,
g_cost_method_type, g_cost_type_code, g_cost_method,
g_default_cost_type_id
FROM org_organization_definitions le, gl_ledgers led,
cm_mthd_mst mthd, gem_lookups lk
WHERE le.organization_id = g_legal_entity_id
AND led.ledger_id = g_ledger_id
AND mthd.cost_type_id = g_cost_type_id
AND lk.lookup_type = 'GMF_COST_METHOD'
AND lk.lookup_code = mthd.cost_type;
SELECT gle.legal_entity_name
INTO g_legal_entity_name
FROM gmf_legal_entities gle
WHERE gle.legal_entity_id = g_legal_entity_id ;
SELECT gl.name
INTO g_ledger_name
FROM gl_ledgers gl
WHERE gl.ledger_id = g_ledger_id;
SELECT mthd.cost_type,
mthd.cost_mthd_code,
lk.meaning,
nvl(mthd.default_lot_cost_type_id, -1)
INTO g_cost_method_type,
g_cost_type_code,
g_cost_method,
g_default_cost_type_id
FROM cm_mthd_mst mthd,
gem_lookups lk
WHERE mthd.cost_type_id = g_cost_type_id
AND lk.lookup_type = 'GMF_COST_METHOD'
AND lk.lookup_code = mthd.cost_type ;
SELECT curr.period_id, prev.period_id,
mthd.cost_mthd_code
INTO g_crev_curr_period_id, g_crev_prev_period_id,
g_crev_prev_cost_mthd
FROM gmf_period_statuses curr, gmf_period_statuses prev, cm_mthd_mst mthd
WHERE curr.legal_entity_id = g_legal_entity_id
AND curr.cost_type_id = g_cost_type_id
AND curr.calendar_code = g_crev_curr_calendar
AND curr.period_code = g_crev_curr_period
AND prev.legal_entity_id = g_legal_entity_id
AND prev.cost_type_id = g_crev_prev_cost_type_id
AND prev.calendar_code = g_crev_prev_calendar
AND prev.period_code = g_crev_prev_period
AND mthd.cost_type_id = g_crev_prev_cost_type_id
;
/* insert the control record */
gmf_util.log(l_procedure_name || ': inserting the control records into gl_subr_sta');
SELECT (l_up_posting_end_date - l_up_posting_start_date)*24*60*60 /* Date range in seconds */
INTO l_date_range_secs FROM DUAL;
insert_control_record(
p_user_id => FND_GLOBAL.user_id,
p_gl_fiscal_year => TO_NUMBER(p_gl_fiscal_year),
p_gl_period => TO_NUMBER(p_gl_period),
p_posting_start_date => l_up_posting_start_date,
p_posting_end_date => l_up_posting_end_date,
p_test_posting => p_test_posting,
p_closed_per_ind => l_closed_per_ind,
p_open_gl_date => FND_DATE.canonical_to_date(p_open_gl_date),
p_crev_gl_trans_date => l_crev_gl_trans_date,
p_open_gl_fiscal_year => l_open_gl_fiscal_year,
p_open_gl_period => l_open_gl_period,
p_post_if_no_cost => p_post_if_no_cost,
p_default_language => USERENV('LANG'),
p_inv_fiscal_year => l_inv_fiscal_year,
p_inv_period => l_inv_period,
x_subledger_ref_no => l_subledger_ref_no,
x_retstatus => l_retstatus,
x_errbuf => l_errbuf
);
UPDATE gl_subr_sta
SET request_id = l_conc_id
WHERE reference_no = l_subledger_ref_no;
SELECT TRUNC(l_date_range_secs/g_num_req) INTO l_secs FROM DUAL;
insert_control_record(
p_user_id => FND_GLOBAL.user_id,
p_gl_fiscal_year => TO_NUMBER(p_gl_fiscal_year),
p_gl_period => TO_NUMBER(p_gl_period),
p_posting_start_date => l_req_start_date,
p_posting_end_date => l_req_end_date,
p_test_posting => p_test_posting,
p_closed_per_ind => l_closed_per_ind,
p_open_gl_date => FND_DATE.canonical_to_date(p_open_gl_date),
p_crev_gl_trans_date => l_crev_gl_trans_date,
p_open_gl_fiscal_year => l_open_gl_fiscal_year,
p_open_gl_period => l_open_gl_period,
p_post_if_no_cost => p_post_if_no_cost,
p_default_language => USERENV('LANG'),
p_inv_fiscal_year => l_inv_fiscal_year,
p_inv_period => l_inv_period,
x_subledger_ref_no => l_subledger_ref_no,
x_retstatus => l_retstatus,
x_errbuf => l_errbuf
);
UPDATE gl_subr_sta
SET request_id = l_conc_id
WHERE reference_no = l_subledger_ref_no;
insert_control_record(
p_user_id => FND_GLOBAL.user_id,
p_gl_fiscal_year => TO_NUMBER(p_gl_fiscal_year),
p_gl_period => TO_NUMBER(p_gl_period),
p_posting_start_date => gmf_legal_entity_tz.convert_le_to_srv_tz(FND_DATE.canonical_to_date(p_posting_start_date), g_legal_entity_id),
p_posting_end_date => gmf_legal_entity_tz.convert_le_to_srv_tz(FND_DATE.canonical_to_date(p_posting_end_date), g_legal_entity_id),
p_test_posting => p_test_posting,
p_closed_per_ind => l_closed_per_ind,
p_open_gl_date => FND_DATE.canonical_to_date(p_open_gl_date),
p_crev_gl_trans_date => l_crev_gl_trans_date,
p_open_gl_fiscal_year => l_open_gl_fiscal_year,
p_open_gl_period => l_open_gl_period,
p_post_if_no_cost => p_post_if_no_cost,
p_default_language => USERENV('LANG'),
p_inv_fiscal_year => l_inv_fiscal_year,
p_inv_period => l_inv_period,
x_subledger_ref_no => l_subledger_ref_no,
x_retstatus => l_retstatus,
x_errbuf => l_errbuf
);
UPDATE gl_subr_sta
SET request_id = l_conc_id
WHERE reference_no = l_subledger_ref_no;
END update_process;
SELECT DISTINCT glp.period_year
FROM
gl_periods glp,
gl_period_sets gps,
gl_sets_of_books gsb
WHERE
glp.period_year = cp_fiscal_year
AND gsb.set_of_books_id = cp_ledger_id
AND gsb.period_set_name = glp.period_set_name
AND gsb.accounted_period_type = glp.period_type
AND glp.period_set_name = gps.period_set_name
;
SELECT glp.period_name, glp.period_year, glp.period_num,
glp.start_date, glp.end_date, sts.closing_status
FROM
gl_periods glp,
gl_period_statuses sts,
gl_sets_of_books gsob
WHERE
glp.period_set_name = gsob.period_set_name -- use the sob period-name
AND glp.period_type = gsob.accounted_period_type -- and sob period-type
AND gsob.set_of_books_id = cp_ledger_id
AND glp.period_year = NVL(cp_gl_fiscal_year, glp.period_year)
AND glp.period_num = NVL(cp_gl_period, glp.period_num)
AND NVL(trunc(cp_gl_date), glp.start_date)
BETWEEN glp.start_date AND glp.end_date
AND glp.period_name = sts.period_name -- for use of sts_u2 index
AND glp.period_num = sts.period_num
AND glp.period_year = sts.period_year
AND sts.set_of_books_id = cp_ledger_id
AND sts.application_id = (
SELECT application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL')
;
l_source_selected BOOLEAN;
l_source_selected := TRUE;
* insert_control_record
*
* DESCRIPTION
* Inserts a control record after verifying there is no other running or
* scheduled process for the same parameters.
*
* INPUT PARAMETERS
* All columns of the control table
*
* ASSUMPTIONS
* All column data have been validated and if CM source is selected then
* appropriate INV calendar parameters have also been validated.
*
* HISTORY
* 26-Dec-2002 Rajesh Seshadri
* Uday Phadtare SEP-08-2008 Bug 7355006. If process_category is 'PRODUCTIONS_TRANSACTIONS'
* then set l_post_pm as 1.
* Uday Phadtare JUL-06-2010 Bug 12622793. Allow parallel pre-processor runs in a period for OM
* source when the date range is not over lapping.
* 29-Jan-2013 pmarada, bug15954309, enable paralle processing enhancement. added new parameters
* num_of_process and post_txn_cmpt_cost.
*************************************************************************************************/
PROCEDURE insert_control_record(
p_user_id IN NUMBER,
p_gl_fiscal_year IN NUMBER,
p_gl_period IN NUMBER,
p_posting_start_date IN DATE,
p_posting_end_date IN DATE,
p_test_posting IN VARCHAR2,
/* Start INVCONV umoogala
p_post_cm IN VARCHAR2,
p_post_ic IN VARCHAR2,
p_post_om IN VARCHAR2,
p_post_op IN VARCHAR2,
p_post_pm IN VARCHAR2,
p_post_po IN VARCHAR2,
p_post_pur IN VARCHAR2,
*/
p_closed_per_ind IN NUMBER,
p_open_gl_date IN DATE,
p_crev_gl_trans_date IN DATE,
p_open_gl_fiscal_year IN NUMBER,
p_open_gl_period IN NUMBER,
p_post_if_no_cost IN VARCHAR2,
p_default_language IN VARCHAR2,
/* Start INVCONV umoogala
p_crev_curr_mthd IN VARCHAR2,
p_crev_curr_calendar IN VARCHAR2,
p_crev_curr_period IN VARCHAR2,
p_crev_prev_mthd IN VARCHAR2,
p_crev_prev_calendar IN VARCHAR2,
p_crev_prev_period IN VARCHAR2,
*/
p_inv_fiscal_year IN VARCHAR2,
p_inv_period IN NUMBER,
x_subledger_ref_no OUT NOCOPY NUMBER,
x_retstatus OUT NOCOPY VARCHAR2,
x_errbuf OUT NOCOPY VARCHAR2
) AS
CURSOR c_sch(cp_le_id NUMBER, cp_ledger_id NUMBER, cp_cost_type_id NUMBER,
cp_gl_fiscal_year NUMBER, cp_gl_period NUMBER,
cp_post_cm VARCHAR2, cp_post_ic VARCHAR2,
cp_post_om VARCHAR2, cp_post_pm VARCHAR2,
cp_post_pur VARCHAR2 )
IS
SELECT reference_no, request_id, count(*) over()
FROM gl_subr_sta
WHERE legal_entity_id = cp_le_id
AND ledger_id = cp_ledger_id
AND cost_type_id = cp_cost_type_id
AND fiscal_year = cp_gl_fiscal_year
AND period = cp_gl_period
AND completion_ind = 0
AND stop_ind = 0
AND rownum = 1
AND ((post_ic = cp_post_ic AND post_ic = 1) OR
(post_pm = cp_post_pm AND post_pm = 1) OR
(post_cm = cp_post_cm AND post_cm = 1) OR
(post_om = cp_post_om AND post_om = 1) OR
(post_pur = cp_post_pur AND post_pur = 1))
;
SELECT reference_no, request_id, count(*) over()
FROM gl_subr_sta
WHERE legal_entity_id = cp_le_id
AND ledger_id = cp_ledger_id
AND cost_type_id = cp_cost_type_id
AND fiscal_year = cp_gl_fiscal_year
AND period = cp_gl_period
AND completion_ind = 0
AND stop_ind = 0
AND rownum = 1
AND (post_om = cp_post_om AND post_om = 1)
AND (
(cp_posting_start_date between period_start_date and period_end_date) OR
(cp_posting_end_date between period_start_date and period_end_date)
)
;
e_insert_error EXCEPTION;
l_procedure_name CONSTANT VARCHAR2(30) := 'INSERT_CONTROL_RECORD';
RAISE e_insert_error;
SELECT period_id
INTO l_period_id
FROM gmf_period_statuses prdsta
WHERE
prdsta.legal_entity_id = g_legal_entity_id
AND prdsta.cost_type_id = g_cost_type_id
AND p_posting_start_date between prdsta.start_date and prdsta.end_date
AND p_posting_end_date between prdsta.start_date and prdsta.end_date
AND prdsta.delete_mark = 0 /* bug14184808 */
;
RAISE e_insert_error;
Select gem5_reference_no_s.NEXTVAL INTO l_subledger_ref_no From Dual;
/* insert the control record */
INSERT INTO gl_subr_sta
(
co_code,
current_state,
start_time,
end_time,
completion_ind,
started_by,
stop_ind,
reference_no,
fiscal_year,
period,
period_start_date,
period_end_date,
test_posting,
scheduled_on,
aborted_by,
update_stage,
errors_found,
errors_posted,
errors_limit,
rows_posted,
abort_reason,
creation_date,
created_by,
delete_mark,
in_use,
last_update_date,
last_updated_by,
closed_per_ind,
gl_date,
gl_fiscal_year,
gl_period,
incl_no_cost,
default_language,
crev_curr_mthd,
crev_curr_calendar,
crev_curr_period,
crev_prev_mthd,
crev_prev_calendar,
crev_prev_period,
crev_gl_trans_date,
crev_inv_prev_cal,
crev_inv_prev_per,
legal_entity_id,
legal_entity_name,
ledger_id,
process_category,
cost_type_id,
period_id,
cost_mthd_code,
cost_type,
default_cost_type_id,
default_cost_mthd_code,
cost_basis,
extract_hdr_rows_posted,
extract_line_rows_posted,
crev_curr_cost_type_id,
crev_curr_period_id,
crev_prev_cost_type_id,
crev_prev_period_id,
post_cm,
post_ic,
post_om,
post_pm,
post_pur,
base_currency,
post_cm_rval, /* PK B13797936 V3 */
post_txn_cmpt_cost,
num_of_process,
batch_id_from,
batch_id_to
)
SELECT
NULL, /* co_code */
0, /* current_state */
NULL, /* start_time */
NULL, /* end_time */
0, /* completion_ind */
p_user_id, /* started_by */
0, /* stop_ind */
l_subledger_ref_no, /* reference_no B7203807 */
p_gl_fiscal_year, /* fiscal_year */
p_gl_period, /* period */
p_posting_start_date, /* period_start_date */
p_posting_end_date, /* period_end_date */
DECODE(p_test_posting,'N',0,1),
FND_DATE.date_to_canonical(SYSDATE), /* scheduled_on - always sysdate */
NULL, /* aborted_by */
0, /* update_stage */
0, /* errors_found */
0, /* errors_posted */
0, /* errors_limit */
0, /* rows_posted */
NULL, /* abort_reason */
SYSDATE, /* creation_date */
p_user_id, /* created_by */
0, /* delete_mark */
0, /* in_use */
SYSDATE, /* last_update_date */
p_user_id, /* last_updated_by */
p_closed_per_ind, /* closed_per_ind */
p_open_gl_date, /* gl_date */
p_open_gl_fiscal_year, /* gl_fiscal_year */
p_open_gl_period, /* gl_period */
DECODE(p_post_if_no_cost,'Y',1,0), /* incl_no_cost */
p_default_language, /* default_language */
g_crev_curr_cost_mthd_code, /* crev_curr_mthd */
g_crev_curr_calendar, /* crev_curr_calendar */
g_crev_curr_period, /* crev_curr_period */
g_crev_prev_cost_mthd, /* crev_prev_mthd */
g_crev_prev_calendar, /* crev_prev_calendar */
g_crev_prev_period, /* crev_prev_period */
g_crev_gl_trans_date,
p_inv_fiscal_year,
p_inv_period,
g_legal_entity_id,
g_legal_entity_name,
g_ledger_id,
g_process_category,
g_cost_type_id,
l_period_id,
g_cost_type_code, /* cost_mthd_code */
g_cost_method_type, /* cost_type */
g_default_cost_type_id,
DECODE(g_default_cost_type_id, NULL, NULL, /* default_lot_cost_mthd */
(SELECT default_lot_cost_mthd from cm_mthd_mst
WHERE cost_type_id = g_default_cost_type_id
AND delete_mark = 0)),
plcy.cost_basis,
0, /* extract_hdr_rows_posted, */
0, /* extract_line_rows_posted, */
g_crev_curr_cost_type_id,
g_crev_curr_period_id,
g_crev_prev_cost_type_id,
g_crev_prev_period_id,
l_post_cm,
l_post_ic,
l_post_om,
l_post_pm,
l_post_pur,
plcy.base_currency_code,
DECODE(g_process_category,'REVALUATION_TRANSACTIONS',1,0), /* PK B13797936 V3 */
g_post_txn_cmpt_cost,
g_num_of_process,
g_from_batch_id,
g_to_batch_id
FROM
gmf_fiscal_policies plcy
WHERE
plcy.legal_entity_id = g_legal_entity_id
AND plcy.delete_mark = 0
;
x_errbuf := l_procedure_name || ': failed to insert control record';
gmf_util.log('failed to insert control record');
RAISE e_insert_error;
gmf_util.log(l_procedure_name || ': ' || sql%rowcount || ' control record inserted into gl_subr_sta table. ' ||
' end of procedure');
WHEN e_insert_error
THEN
gmf_util.log(x_errbuf);
END insert_control_record;
SELECT start_date, end_date
FROM gmf_period_statuses
WHERE period_id = cp_period_id
;
SELECT COUNT(1)
FROM gmf_period_statuses
WHERE legal_entity_id = cp_le_id
-- AND cost_type_id = cp_cost_type_id
AND start_date >= cp_prior_end_date
AND end_date <= cp_curr_start_date
AND delete_mark = 0 /* bug14184808 */
;
SELECT closed_period_ind
FROM ic_cldr_dtl
WHERE
orgn_code = cp_co_code
AND fiscal_year = cp_inv_fiscal_year
AND period = cp_inv_period;
SELECT preprd.period_id
FROM gmf_period_statuses prdsta,
gmf_period_statuses preprd
WHERE prdsta.legal_entity_id = p_legal_entity_id
AND prdsta.cost_type_id = p_cost_type_id
AND preprd.legal_entity_id = prdsta.legal_entity_id
AND preprd.cost_type_id = prdsta.cost_type_id
AND p_period_start_date between prdsta.start_date and prdsta.end_date
AND p_period_end_date between prdsta.start_date and prdsta.end_date
AND preprd.end_date < prdsta.end_date
AND preprd.delete_mark = 0 /* bug14184808 */
ORDER BY preprd.end_date desc;
SELECT NVL(SUM(DECODE(NVL(gpb.period_close_status,DECODE(oap.open_flag,'Y','~','P')),
'F',1,'P',1, 0)),0) AS close_status
FROM org_acct_periods oap,
hr_organization_information hoi,
mtl_parameters mp,
gmf_period_statuses gps,
gl_ledgers gl,
gmf_period_balances gpb
WHERE gps.period_id = p_period_id
AND hoi.org_information2 = gps.legal_entity_id
AND hoi.org_information1 = gl.ledger_id
AND oap.period_set_name = gl.period_set_name
AND hoi.org_information_context = 'Accounting Information'
AND hoi.organization_id = oap.organization_id
AND hoi.organization_id = mp.organization_id
AND mp.process_enabled_flag = 'Y'
AND oap.schedule_close_date = TRUNC(gmf_legal_entity_tz.convert_srv_to_le(g_legal_entity_id,gps.end_date)) -- PK B13797936
AND oap.organization_id = gpb.organization_id(+)
AND oap.acct_period_id = gpb.acct_period_id(+);
SELECT period_year,
period_num
INTO x_inv_fiscal_year,
x_inv_period
FROM org_acct_periods oap,
hr_organization_information hoi,
gmf_period_statuses gps,
gl_ledgers gl
WHERE gps.period_id = l_prior_period_id
AND hoi.org_information2 = gps.legal_entity_id
AND hoi.org_information1 = gl.ledger_id
AND oap.period_set_name = gl.period_set_name
AND hoi.org_information_context = 'Accounting Information'
AND hoi.organization_id = oap.organization_id
AND oap.schedule_close_date = TRUNC(gmf_legal_entity_tz.convert_srv_to_le(g_legal_entity_id,gps.end_date)) -- PK B13797936 V3
AND ROWNUM = 1;
# 12-Nov-99 Rajesh Seshadri Bug 1064535 - use delete_mark on ic_cldr_hdr
############################################################################# */
PROCEDURE inter_mod_cal_conv(
x_inv_fiscal_year OUT NOCOPY NUMBER,
x_inv_period OUT NOCOPY NUMBER,
x_inv_per_synch OUT NOCOPY VARCHAR2,
x_inv_per_start_date OUT NOCOPY DATE,
x_inv_per_end_date OUT NOCOPY DATE,
x_retstatus OUT NOCOPY VARCHAR2,
x_errbuf OUT NOCOPY VARCHAR2 )
IS
/* Start INVCONV umoogala
CURSOR c_cmsrc_info(cp_co_code VARCHAR2, cp_source_calendar VARCHAR2, cp_source_period VARCHAR2) IS
*/
CURSOR c_cmsrc_info(cp_period_id number)
IS
SELECT start_date, end_date
FROM gmf_period_statuses
WHERE period_id = cp_period_id
;
SELECT
d1.period_year fiscal_year, d1.period_num period,
d1.period_start_date begin_date, d2.schedule_close_date period_end_date
FROM
org_organization_definitions org,
org_acct_periods d1,
org_acct_periods d2
WHERE
org.legal_entity = cp_le_id
AND d2.period_year = d1.period_year
AND org.organization_id = d1.organization_id
AND org.organization_id = d2.organization_id
AND TRUNC(d1.schedule_close_date+1-1/86400) = TRUNC(cp_cm_end_date)
AND d2.schedule_close_date <= d1.schedule_close_date
ORDER BY
d2.schedule_close_date desc
;
SELECT le.organization_name, led.name,
mthd.cost_type, mthd.cost_mthd_code, lk.meaning,
mthd.default_lot_cost_type_id
INTO g_legal_entity_name, g_ledger_name,
g_cost_method_type, g_cost_type_code, g_cost_method,
g_default_cost_type_id
FROM org_organization_definitions le, gl_ledgers led,
cm_mthd_mst mthd, gem_lookups lk
WHERE le.organization_id = g_legal_entity_id
AND led.ledger_id = g_ledger_id
AND mthd.cost_type_id = g_cost_type_id
AND lk.lookup_type = 'GMF_COST_METHOD'
AND lk.lookup_code = mthd.cost_type;
SELECT gle.legal_entity_name
INTO g_legal_entity_name
FROM gmf_legal_entities gle
WHERE gle.legal_entity_id = g_legal_entity_id ;
SELECT gl.name
INTO g_ledger_name
FROM gl_ledgers gl
WHERE gl.ledger_id = g_ledger_id;
SELECT mthd.cost_type,
mthd.cost_mthd_code,
lk.meaning,
nvl(mthd.default_lot_cost_type_id, -1)
INTO g_cost_method_type,
g_cost_type_code,
g_cost_method,
g_default_cost_type_id
FROM cm_mthd_mst mthd,
gem_lookups lk
WHERE mthd.cost_type_id = g_cost_type_id
AND lk.lookup_type = 'GMF_COST_METHOD'
AND lk.lookup_code = mthd.cost_type ;
SELECT curr.period_id, prev.period_id,
mthd.cost_mthd_code
INTO g_crev_curr_period_id, g_crev_prev_period_id,
g_crev_prev_cost_mthd
FROM gmf_period_statuses curr, gmf_period_statuses prev, cm_mthd_mst mthd
WHERE curr.legal_entity_id = g_legal_entity_id
AND curr.cost_type_id = g_cost_type_id
AND curr.calendar_code = g_crev_curr_calendar
AND curr.period_code = g_crev_curr_period
AND prev.legal_entity_id = g_legal_entity_id
AND prev.cost_type_id = g_crev_prev_cost_type_id
AND prev.calendar_code = g_crev_prev_calendar
AND prev.period_code = g_crev_prev_period
AND mthd.cost_type_id = g_crev_prev_cost_type_id
;