[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x'
FROM pa_organizations_expend_v
WHERE organization_id = x_org_id
and active_flag = 'Y'
and trunc(x_txn_date) between date_from and nvl(date_to,trunc(x_txn_date));
select count(*)
into x_dummy
from pa_expenditure_types_expend_v
where x_ei_date between expnd_typ_start_date_active
and nvl(expnd_typ_end_date_active,x_ei_date)
and x_ei_date between SYS_LINK_START_DATE_ACTIVE
and nvl(sys_link_end_date_active,x_ei_date)
and system_linkage_function = x_sys_link_func
and expenditure_type = x_exp_type;
SELECT NVL(NET_ZERO_ADJUSTMENT_FLAG,'N')
INTO x_return_flag
FROM PA_EXPENDITURE_ITEMS
WHERE EXPENDITURE_ITEM_ID = x_exp_item_id;
SELECT
'Y'
INTO
l_dummy
FROM
pa_expend_item_adj_activities eia
WHERE
eia.expenditure_item_id = X_exp_id
AND eia.exception_activity_code = 'SOURCE ITEM PURGED';
SELECT
'Y'
INTO
l_dummy
FROM
pa_expend_item_adj_activities eia
WHERE
eia.expenditure_item_id = X_exp_id
AND eia.exception_activity_code = 'DESTINATION ITEM PURGED';
select release_name
from fnd_product_groups ;
SELECT T.labor_cost_multiplier_name
INTO l_lcm_name
FROM PA_TASKS T
WHERE T.task_id = x_task_id;
SELECT org_id
INTO l_org_id
FROM pa_projects_all
WHERE project_id = p_project_id ;
SELECT p.org_id
INTO l_org_id
FROM pa_projects_all p,
pa_tasks t
WHERE p.project_id = t.project_id
AND t.task_id = p_task_id ;
* SQL to select the earliest open PA_DATE
* Select the earliest open date only if the global earliest date is not yet populated.
* Because , earliest pa_date will remain the same for a run.
*/
IF ( p_caller_flag = 'R' AND g_r_earliest_pa_start_date IS NULL ) OR
( p_caller_flag = 'P' AND g_p_earliest_pa_start_date IS NULL ) THEN
l_stage := 20;
SELECT pap1.start_date
,pap1.end_date
,pap1.period_name
INTO l_earliest_start_date
,l_earliest_end_date
,l_earliest_period_name
FROM pa_periods_all pap1
WHERE pap1.status in ('O','F')
AND pap1.org_id = p_org_id /* Bug#9048873 */
AND pap1.start_date = ( SELECT MIN (pap.start_date)
FROM pa_periods_all pap
WHERE pap.status in ('O','F')
AND pap.org_id = p_org_id /* Bug#9048873 */
);
SELECT p_ei_date
,pap.start_date
,pap.end_date
,pap.period_name
INTO l_pa_date
,l_start_date
,l_end_date
,l_period_name
FROM pa_periods_all pap
WHERE pap.status in ('O','F')
AND trunc(p_ei_date) between pap.start_date and pap.end_date
AND pap.org_id = p_org_id ; --removed nvl for the bug#6343739
* Select the immediate available open or future period.
*/
SELECT pap1.start_date
,pap1.start_date
,pap1.end_date
,pap1.period_name
INTO l_pa_date
,l_start_date
,l_end_date
,l_period_name
FROM pa_periods_all pap1
WHERE pap1.status in ('O','F')
AND nvl( pap1.org_id, -99 ) = nvl( p_org_id, -99 )
AND pap1.start_date = ( SELECT MIN (pap.start_date)
FROM pa_periods_all pap
WHERE pap.status in ('O','F')
AND pap.org_id = p_org_id --removed nvl for the bug#6343739
AND trunc(p_ei_date) <= pap.start_date
);
SELECT pap.end_date
,pap.start_date
,pap.end_date
,pap.period_name
INTO l_pa_date
,l_start_date
,l_end_date
,l_period_name
FROM pa_periods_all pap
WHERE pap.status in ('O','F')
AND trunc(p_ei_date) between pap.start_date and pap.end_date
AND pap.org_id = p_org_id; /*removed nvl for bug 9284457 */
* Select the immediate available open or future period.
*/
SELECT pap1.end_date
,pap1.start_date
,pap1.end_date
,pap1.period_name
INTO l_pa_date
,l_start_date
,l_end_date
,l_period_name
FROM pa_periods_all pap1
WHERE pap1.status in ('O','F')
AND pap1.org_id = p_org_id /* Bug#9048873 */
AND pap1.start_date = ( SELECT MIN (pap.start_date)
FROM pa_periods_all pap
WHERE pap.status in ('O','F')
AND pap.org_id = p_org_id /* Bug#9048873 */
AND trunc(p_ei_date) <= pap.start_date
);
SELECT decode(nvl(g_profile_value,'N'),
'Y', pa_utils2.get_pa_date( exp_item_date,accounting_date,org_id),
'N', pa_integration.get_raw_cdl_pa_date(exp_item_date,accounting_date,org_id))
INTO l_return_date
FROM DUAL;
SELECT decode(nvl(g_profile_value,'N'),
'Y', pa_utils2.get_period_name(),
'N', pa_integration.get_period_name())
INTO l_return_name
FROM DUAL;
/* Bug 4374769 : The cursor c_sel_cdl is modified to also select the line_num for a cdl with transfer_status_code as 'Y'.
This line_num is passed to Pa_Costing.ReverseCdl when it is being called from the
populate_gl_dates procedure to create reversing and new lines for the line_num that is being passed. */
Cursor c_sel_cdl Is
SELECT
ei.expenditure_item_id,
cdl.billable_flag,
cdl.line_type,
cdl.line_num, -- Added as part of Bug 4374769
ei.transaction_source,
tr.gl_accounted_flag,
ei.denom_currency_code,
ei.acct_currency_code,
ei.acct_rate_date,
ei.acct_rate_type,
ei.acct_exchange_rate,
ei.project_currency_code,
ei.project_rate_date,
ei.project_rate_type,
ei.project_exchange_rate,
tr.system_linkage_function,
ei.projfunc_currency_code,
ei.projfunc_cost_rate_date,
ei.projfunc_cost_rate_type,
ei.projfunc_cost_exchange_rate,
ei.work_type_id
FROM pa_expenditure_items_all ei,
pa_cost_distribution_lines cdl,
pa_transaction_sources tr
WHERE tr.transaction_source(+) = ei.transaction_source
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND CDL.Transfer_Status_Code = 'Y';
select interface_labor_to_gl_flag , interface_usage_to_gl_flag , XFACE_CWK_LABOR_TO_GL_FLAG , set_of_books_id
Into l_labor_to_gl , l_usage_to_gl , l_cwk_lab_to_gl , l_sob_id
from pa_implementations;
SELECT
ei.expenditure_item_id,
ei.adjusted_expenditure_item_id,
cdl.gl_date,
cdl.pji_summarized_flag,
cdl.billable_flag,
cdl.line_type,
cdl.line_num,
ei.denom_currency_code,
ei.acct_currency_code,
ei.acct_rate_date,
ei.acct_rate_type,
ei.acct_exchange_rate,
ei.project_currency_code,
ei.project_rate_date,
ei.project_rate_type,
ei.project_exchange_rate,
ei.projfunc_currency_code,
ei.projfunc_cost_rate_date,
ei.projfunc_cost_rate_type,
ei.projfunc_cost_exchange_rate,
ei.work_type_id
INTO l_exp_item_id,
l_adj_exp_item_id,
l_gl_date,
l_pji_summarized_flag,
l_billable_flag,
l_line_type,
l_line_num,
l_denom_currency_code,
l_acct_currency_code,
l_acct_rate_date,
l_acct_rate_type,
l_acct_exchange_rate,
l_project_currency_code,
l_project_rate_date,
l_project_rate_type,
l_project_exchange_rate,
l_projfunc_currency_code,
l_projfunc_cost_rate_date,
l_projfunc_cost_rate_type,
l_projfunc_cost_exchange_rate,
l_work_type_id
FROM PA_COST_DISTRIBUTION_LINES_ALL CDL,
PA_EXPENDITURE_ITEMS_ALL EI
WHERE CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND CDL.ROWID = chartorowid( p_cdl_rowid(1));
/* Bug 4374769 : The code in the "PRC: Interface and Usage Transactions to General Ledger" process that updated the CDLs of reversing EI with
next GL period of CDL of original EI, is shifted to populate_gl_dates as below */
/* Bug 4374769 : The following query selects the end_date of the GL period stamped on the 'R' line of the original expenditure item. */
SELECT GPS.end_date
INTO v_gl_per_end_dt
FROM pa_cost_distribution_lines CDL,
gl_period_statuses GPS
WHERE GPS.application_id = 101
AND GPS.set_of_books_id = l_sob_id
AND GPS.adjustment_period_flag = 'N'
AND CDL.expenditure_item_id = l_adj_exp_item_id
AND CDL.gl_date BETWEEN GPS.start_date AND GPS.end_date
AND CDL.LINE_TYPE = 'R';
/* Bug 4374769 : If the date selected in the above query is greater than or equal to the GL date on the cdl of the reversing EI and
a) If the PJI_Summarized_flag on the cdl is 'N' then we directly update the GL_Date of the cdl with the start date
of a GL Period that is next to that of the cdl of the original EI.
b) If the the PJI_Summarized_flag on the cdl is NULL then the ReverseCdl procedure is called to create the reversing
and new 'I' lines. Finally we update the GL_Date of the 'R' and the new 'I' line with the start date of a GL Period
that is next to that of the cdl of the original EI. */
IF (l_gl_date <= v_gl_per_end_dt) THEN
SELECT GPS.start_date
INTO l_prvdr_accr_date
FROM gl_period_statuses GPS
WHERE GPS.application_id = 101
AND GPS.set_of_books_id = l_sob_id
AND GPS.adjustment_period_flag = 'N'
AND GPS.start_date = (SELECT min(GPS1.start_date)
FROM gl_period_statuses GPS1
WHERE GPS1.application_id = 101
AND GPS1.set_of_books_id = l_sob_id
AND GPS1.adjustment_period_flag = 'N'
AND GPS1.start_date > v_gl_per_end_dt);
UPDATE PA_Cost_Distribution_lines CDL
SET CDL.gl_date = l_prvdr_accr_date
WHERE CDL.ROWID = chartorowid( p_cdl_rowid(1))
AND CDL.TRANSFER_STATUS_CODE in ('P','R');
UPDATE PA_Cost_Distribution_lines CDL
SET CDL.GL_DATE = l_prvdr_accr_date,
CDL.GL_PERIOD_NAME = pa_utils2.get_gl_period_name (l_prvdr_accr_date,CDL.org_id)
WHERE CDL.EXPENDITURE_ITEM_ID = l_exp_item_id
AND CDL.LINE_NUM_REVERSED IS NULL
AND CDL.TRANSFER_STATUS_CODE in ('P','R','G');
* But, since the SELECT is kind-of static i.e., its enough
* that it be executed only once per call, i'm retaining it here.
* In-fact because of its staticness, it can be even moved out of this
* package and retained in patmv.lpc and the reject_reason can be passed
* as parameter.
*/
SELECT Meaning
INTO l_reject_meaning
FROM PA_Lookups LOOK
WHERE LOOK.Lookup_Type = 'TRANSFER REJECTION CODE'
AND LOOK.Lookup_Code = 'TRANS_INV_DATA';
UPDATE PA_Cost_Distribution_lines CDL
SET CDL.request_id = p_request_id(i)
,CDL.transfer_rejection_reason = l_reject_meaning
,CDL.transfer_status_code = 'X'
,CDL.Transferred_Date = SYSDATE
/*
* Bug#2085814
* -- Since gl period information is getting populated during costing, it is no
* -- longer needed to populate GL info during transfer to AP.
* -- Ideally this procedure itself need not be called from patmv.lpc. The updates
* -- to other columns like request_id can be done in patmv.lpc itself.
* -- Calling this procedure from pro*C requires some array related processing which
* -- can be avoided if this procedure is not called from pro*C. This change has to
* -- be done - at some point of time.
*
* ,CDL.GL_Date = ( SELECT pa_utils2.get_prvdr_gl_date(
* MAX(CDL.pa_date)
* ,p_application_id(i)
* ,p_prvdr_sob_id(i))
* FROM pa_cost_distribution_lines CDL,
* pa_expenditure_items ITEM
* WHERE ITEM.expenditure_item_id = CDL.expenditure_item_id
* AND CDL.line_type = 'R'
* AND ITEM.expenditure_id = p_expnd_id(i)
* )
* ,CDL.Recvr_Gl_Date = ( SELECT pa_utils2.get_recvr_gl_date(
* MAX(CDL.recvr_pa_date)
* ,p_application_id(i)
* ,p_recvr_sob_id(i))
* FROM pa_cost_distribution_lines CDL,
* pa_expenditure_items ITEM
* WHERE ITEM.expenditure_item_id = CDL.expenditure_item_id
* AND CDL.line_type = 'R'
* AND ITEM.expenditure_id = p_expnd_id(i)
* )
*/
WHERE CDL.Transfer_Status_Code || '' IN ('P','R')
AND CDL.line_type = 'R'
AND CDL.Batch_name IS NOT NULL
AND CDL.Expenditure_Item_ID IN
(
SELECT ITEM.Expenditure_Item_ID
FROM PA_Expenditure_Items ITEM
WHERE ITEM.Cost_Distributed_Flag||'' = 'S'
AND ITEM.expenditure_id = p_expnd_id(i)
);
(1)If these CDLs have not been summarized the CDLs would be updated.
(2)If these CDLs have been summarized then the CDL would be reversed and a new line
created with the rederived GL date while all other attribute would remain same
including the PA Dates.
****************************************************************************************/
IF gms_pa_api2.is_grants_enabled = 'N' THEN
-- Commented for Bug 4374769
/* select interface_labor_to_gl_flag , interface_usage_to_gl_flag , XFACE_CWK_LABOR_TO_GL_FLAG
Into l_labor_to_gl , l_usage_to_gl , l_cwk_lab_to_gl
from pa_implementations; */
/* 4130583 - The following SELECT raises "no data found" exception if the interface flag
corresponding to the CDL is unchecked. The exception block will set the date and
period values in the local PL/SQL table to NULL and set l_interface_to_gl(i) to 'N' */
BEGIN
Select
pa_utils2.get_prvdr_gl_date(
CDL.gl_date
,p_application_id(i)
,p_prvdr_sob_id(i)) gl_date,
pa_utils2.get_gl_period_name (
pa_utils2.get_prvdr_gl_date(
CDL.gl_date
,p_application_id(i)
,p_prvdr_sob_id(i))
,CDL.org_id) gl_period_name,
pa_utils2.get_recvr_gl_date(
CDL.recvr_gl_date
,p_application_id(i)
,p_recvr_sob_id(i)) recvr_gl_date,
pa_utils2.get_gl_period_name (
pa_utils2.get_recvr_gl_date(
CDL.recvr_gl_date
,p_application_id(i)
,p_recvr_sob_id(i))
,nvl(EI.recvr_org_id,CDL.org_id)) recvr_gl_period_name,
'Y' -- Interface to GL
Into l_gl_date_new(i) , l_gl_period_new(i) , l_recvr_gl_date_new(i) , l_recvr_gl_period_new(i) , l_interface_to_gl(i)
From PA_Cost_Distribution_lines CDL,PA_Expenditure_items_all EI,PA_Expenditures EXP
Where CDL.Rowid = chartorowid( p_cdl_rowid(i) )
AND CDL.Transfer_Status_Code in ('P','R')
AND CDL.expenditure_item_id = EI.expenditure_item_id
AND EXP.expenditure_id = EI.Expenditure_Id
AND decode(EI.system_linkage_function /* If the interface to GL is not ticked we donot rederive the GL Dates */
,'ST',nvl(Decode(nvl(EXP.person_type, 'EMP')
,'EMP',l_labor_to_gl
,l_cwk_lab_to_gl),'N')
,'OT',nvl(Decode(nvl(EXP.person_type, 'EMP')
,'EMP',l_labor_to_gl
,l_cwk_lab_to_gl),'N')
,'VI','Y'
,'ER','Y'
,nvl(l_usage_to_gl,'N')) = 'Y';
/* When the CDL is not summarized then update the CDLs with the rederived GL dates. */
/* Bug 3669746 : Modified the update to check if the derived gl date/receiver gl date is null
i.e. no future open periods exist then update the transfer_status_code to 'R' and reason with appropriate value.
In case of rejection the date and period info is not nulled out.
*/
/* 4130583 - l_interface_to_gl(i),CDL GL Date/Period and Receiver GL Date/Period will never be NULL.
Modified update stmt accordingly */
/* Bug 4374769 : Both PJI Summarized and Non Summarized cdls are handled in a single update statement. The logic is as follows :
a) When the CDL is not summarized then update the CDLs with the rederived GL dates.
b) When the CDL to be transferred is already summarized the transfer_status_code
is updated to 'X' if the Gl_date on the CDL is still in open period.
If the GL period is closed then we mark the transfer_status_code to intermediate status 'Y'.
For all CDL's stamped with 'Y' reversing line and new line with proper GL info will be created subsequently.
(1)The reversing CDL would be created with line_type as 'I',transfer_status_code as 'G' and the same GL Date/Period as
that of the original 'R' cdl.
(2)The new CDL would be created with line_type as 'I', transfer_status_code as 'G' and GL Date/Period as those of the
next open GL Period .
(3)The Original CDL would be updated with transfer_status_code as 'X' and GL Date/Period as those of the next open
GL Period. The reversed_flag will be NULL for the original 'R' cdl. */
FORALL i IN 1..p_local_set_size
UPDATE PA_Cost_Distribution_lines CDL
SET CDL.request_id = p_request_id(i)
,CDL.transfer_status_code = Decode(l_interface_to_gl(i),
'Y', DECODE(l_gl_date_new(i),
NULL,'R',
DECODE(l_recvr_gl_date_new(i)
,NULL,'R',
DECODE (CDL.PJI_SUMMARIZED_FLAG,
'N', 'X',
DECODE (CDL.gl_date ,
l_gl_date_new(i), 'X',
'Y'
)
)
)
),
'X'
)
,CDL.Transfer_Rejection_Reason =
(
SELECT Meaning
FROM PA_Lookups LOOK
WHERE LOOK.Lookup_Type = 'TRANSFER REJECTION CODE'
AND LOOK.Lookup_Code = Decode(l_interface_to_gl(i),'Y'
,DECODE(l_gl_date_new(i),NULL,'NO_GL_DATE'
,DECODE(l_recvr_gl_date_new(i),NULL,'NO_RECVR_GL_DATE',NULL)),NULL)
)
,CDL.Transferred_Date = SYSDATE
,CDL.gl_date = Decode(l_interface_to_gl(i),
'N',CDL.gl_date,
DECODE ( CDL.PJI_SUMMARIZED_FLAG,
'N', nvl(l_gl_date_new(i),CDL.gl_date) ,
CDL.GL_DATE
)
)
,CDL.gl_period_name = Decode(l_interface_to_gl(i),
'N',CDL.gl_period_name,
DECODE ( CDL.PJI_SUMMARIZED_FLAG,
'N', nvl(l_gl_period_new(i),CDL.gl_period_name),
CDL.gl_period_name
)
)
,CDL.recvr_gl_date = Decode(l_interface_to_gl(i),
'N',CDL.recvr_gl_date,
DECODE ( CDL.PJI_SUMMARIZED_FLAG,
'N', nvl(l_recvr_gl_date_new(i),CDL.recvr_gl_date),
CDL.recvr_gl_date
)
)
,CDL.recvr_gl_period_name = Decode(l_interface_to_gl(i),
'N', CDL.recvr_gl_period_name ,
DECODE ( CDL.PJI_SUMMARIZED_FLAG,
'N', nvl(l_recvr_gl_period_new(i),CDL.recvr_gl_period_name),
CDL.recvr_gl_period_name
)
)
WHERE CDL.Rowid = chartorowid( p_cdl_rowid(i) )
AND CDL.Transfer_Status_Code in ('P','R') ; /* Bug#3114404 */
UPDATE PA_Cost_Distribution_lines CDL
SET CDL.request_id = p_request_id(i)
,CDL.transfer_status_code = DECODE(l_gl_date_new(i) -- Bug 3669746
, NULL ,'R'
,DECODE(l_recvr_gl_date_new(i)
,NULL,'R'
,Decode(CDL.gl_date
,l_gl_date_new(i),'X'
,'Y')))
,CDL.Transfer_Rejection_Reason = -- Bug 3669746
(
SELECT Meaning
FROM PA_Lookups LOOK
WHERE LOOK.Lookup_Type = 'TRANSFER REJECTION CODE'
AND LOOK.Lookup_Code = DECODE(l_gl_date_new(i)
, NULL,'NO_GL_DATE'
,DECODE(l_recvr_gl_date_new(i)
,NULL,'NO_RECVR_GL_DATE'
,NULL))
)
,CDL.Transferred_Date = decode (CDL.gl_date
,l_gl_date_new(i),SYSDATE
,NULL)
WHERE CDL.Rowid = chartorowid( p_cdl_rowid(i) )
AND CDL.Transfer_Status_Code in ('P','R')
AND CDL.pji_summarized_flag is NULL ; */
UPDATE PA_Cost_Distribution_lines CDL
SET CDL.request_id = p_request_id(i)
,CDL.transfer_status_code = 'G'
,CDL.Transferred_Date = SYSDATE
WHERE CDL.Transfer_Status_Code in ('Y')
AND (CDL.line_num_reversed is NOT NULL
OR CDL.reversed_flag = 'Y'); */
UPDATE PA_Cost_Distribution_lines CDL
SET CDL.request_id = p_request_id(i)
,CDL.transfer_status_code = 'X'
,CDL.Transferred_Date = SYSDATE
,CDL.gl_date = Decode(l_interface_to_gl(i),'N',CDL.gl_date,nvl(l_gl_date_new(i),CDL.gl_date))
,CDL.gl_period_name = Decode(l_interface_to_gl(i),'N',CDL.gl_period_name,nvl(l_gl_period_new(i),CDL.gl_period_name))
,CDL.recvr_gl_date = Decode(l_interface_to_gl(i),'N',CDL.recvr_gl_date,nvl(l_recvr_gl_date_new(i),CDL.recvr_gl_date))
,CDL.recvr_gl_period_name = Decode(l_interface_to_gl(i),'N',CDL.recvr_gl_period_name
,nvl(l_recvr_gl_period_new(i),CDL.recvr_gl_period_name))
WHERE CDL.Transfer_Status_Code in ('Y')
AND CDL.reversed_flag is NULL;
UPDATE PA_Cost_Distribution_lines CDL
SET CDL.request_id = p_request_id(i)
,CDL.transfer_status_code = 'X'
,CDL.Transferred_Date = SYSDATE
WHERE CDL.Rowid = chartorowid( p_cdl_rowid(i) )
AND CDL.Transfer_Status_Code in ('P','R'); /* Bug#3114404 */
SELECT PERIOD.start_date,
PERIOD.end_date,
PERIOD.period_name
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.application_id = p_application_id
AND PERIOD.set_of_books_id = p_set_of_books_id
AND PERIOD.closing_status||'' IN ('O','F')
AND PERIOD.adjustment_period_flag = 'N'
AND trunc(c_reference_date) BETWEEN PERIOD.start_date and PERIOD.end_date;
SELECT PERIOD.start_date
,PERIOD.end_date
,PERIOD.period_name
INTO l_earliest_start_date
,l_earliest_end_date
,l_earliest_period_name
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.set_of_books_id = p_set_of_books_id
AND PERIOD.application_id = p_application_id
AND PERIOD.adjustment_period_flag = 'N'
AND PERIOD.end_date = (
SELECT MIN (PERIOD1.end_date)
FROM GL_PERIOD_STATUSES PERIOD1
WHERE PERIOD1.closing_status in ('O','F')
AND PERIOD1.application_id = p_application_id /* Bug# 1899771 */
AND PERIOD1.adjustment_period_flag = 'N' /* Bug# 1899771 */
AND PERIOD1.set_of_books_id = p_set_of_books_id) ;
SELECT PERIOD.start_date,
PERIOD.end_date,
PERIOD.end_date,
PERIOD.period_name
INTO l_start_date,
l_end_date,
l_gl_date,
l_period_name
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.application_id = p_application_id
AND PERIOD.set_of_books_id = p_set_of_books_id
AND PERIOD.effective_period_num =
( SELECT min(PERIOD1.effective_period_num)
FROM GL_PERIOD_STATUSES PERIOD1
WHERE PERIOD1.application_id = p_application_id
AND PERIOD1.set_of_books_id = p_set_of_books_id
AND PERIOD1.closing_status||'' IN ('O','F')
AND PERIOD1.adjustment_period_flag = 'N'
AND PERIOD1.effective_period_num >=
( SELECT PERIOD2.effective_period_num
FROM GL_PERIOD_STATUSES PERIOD2,
GL_DATE_PERIOD_MAP DPM,
GL_SETS_OF_BOOKS SOB
WHERE SOB.set_of_books_id = p_set_of_books_id
AND DPM.period_set_name = SOB.period_set_name
AND DPM.period_type = SOB.accounted_period_type
AND trunc(DPM.accounting_date) = trunc(p_reference_date)
AND DPM.period_name = PERIOD2.period_name
AND PERIOD2.application_id = p_application_id
AND PERIOD2.set_of_books_id = p_set_of_books_id ))
AND PERIOD.End_Date >= TRUNC(p_reference_date)
AND PERIOD.set_of_books_id = p_set_of_books_id ;
SELECT PERIOD.start_date
,PERIOD.start_date
,PERIOD.end_date
,PERIOD.period_name
INTO l_gl_date
,l_start_date
,l_end_date
,l_period_name
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.application_id = p_application_id
AND PERIOD.set_of_books_id = p_set_of_books_id
AND PERIOD.effective_period_num =
( SELECT min(PERIOD1.effective_period_num)
FROM GL_PERIOD_STATUSES PERIOD1
WHERE PERIOD1.application_id = p_application_id
AND PERIOD1.set_of_books_id = p_set_of_books_id
AND PERIOD1.closing_status||'' IN ('O','F')
AND PERIOD1.adjustment_period_flag = 'N'
AND PERIOD1.effective_period_num >=
( SELECT PERIOD2.effective_period_num
FROM GL_PERIOD_STATUSES PERIOD2,
GL_DATE_PERIOD_MAP DPM,
GL_SETS_OF_BOOKS SOB
WHERE SOB.set_of_books_id = p_set_of_books_id
AND DPM.period_set_name = SOB.period_set_name
AND DPM.period_type = SOB.accounted_period_type
AND trunc(DPM.accounting_date) = trunc(p_reference_date)
AND DPM.period_name = PERIOD2.period_name
AND PERIOD2.application_id = p_application_id
AND PERIOD2.set_of_books_id = p_set_of_books_id ))
AND PERIOD.Start_Date > TRUNC(p_reference_date);
SELECT
ho.organization_id
INTO
x_business_group_id
FROM
hr_all_organization_units ho
WHERE
ho.name = P_Business_Group_Name
AND
ho.organization_id = ho.business_group_id; /* Added this clause for bug 1649495 */
SELECT
person_id
INTO
X_person_id
FROM
per_people_f
WHERE
decode(p_person_type,'CWK', npw_number,employee_number) = P_Employee_Number
AND (business_group_id = P_Business_Group_Id
OR P_Business_Group_Id is NULL)
AND trunc(P_EiDate) between trunc(effective_start_date) and trunc(effective_end_date);
SELECT
person_id
INTO
X_person_id
FROM
per_people_f
WHERE
employee_number = P_Employee_Number
AND (business_group_id = P_Business_Group_Id
OR P_Business_Group_Id is NULL)
AND trunc(P_EiDate) between trunc(effective_start_date) and trunc(effective_end_date);
SELECT
person_id
INTO
X_person_id
FROM
per_people_f
WHERE
npw_number = P_Employee_Number
AND (business_group_id = P_Business_Group_Id
OR P_Business_Group_Id is NULL)
AND trunc(P_EiDate) between trunc(effective_start_date) and trunc(effective_end_date);
SELECT NVL(imp.org_id, -99)
,imp.set_of_books_id
INTO l_org_id
,l_sob_id
FROM pa_implementations imp;
SELECT glp.period_name
,glp.start_date
,glp.end_date
INTO l_gl_period_name
,l_gl_period_start_date
,l_gl_period_end_date
FROM gl_periods glp
,gl_sets_of_books glsob
,pa_implementations_all imp
WHERE glsob.period_set_name = glp.period_set_name
AND glp.period_type = glsob.accounted_period_type
AND glp.adjustment_period_flag <> 'Y'
AND glsob.set_of_books_id = imp.set_of_books_id
AND TRUNC(p_gl_date) BETWEEN TRUNC(glp.start_date) AND TRUNC(glp.end_date)
AND imp.org_id = p_org_id; --removed nvl for bug#6343739
SELECT imp.set_of_books_id
INTO l_set_of_books_id
FROM pa_implementations_all imp
WHERE NVL(imp.org_id,-99) = NVL(p_org_id, -99);
SELECT pap.end_date
INTO l_end_date
FROM pa_periods pap
WHERE trunc(p_date) between pap.start_date AND pap.end_date ;
SELECT papl.end_date,papl.start_date,papl.period_name,papl.gl_period_name
INTO l_org_pa_end_date,l_org_pa_start_date,l_org_pa_period_name,l_org_gl_period_name
FROM pa_periods_all papl
WHERE nvl(papl.org_id, -99 ) = nvl( p_org_id, -99 )
AND trunc(p_ei_date) between papl.start_date and papl.end_date;
SELECT papl.end_date,papl.start_date,papl.period_name
INTO l_org_pa_end_date,l_org_pa_start_date,l_org_pa_period_name
FROM pa_periods_all papl
WHERE nvl(papl.org_id, -99 ) = nvl( p_org_id, -99 )
AND papl.gl_period_name=p_gl_period
AND papl.start_date= ( SELECT MAX(papl1.start_date)
FROM pa_periods_all papl1
WHERE nvl(papl1.org_id, -99 ) = nvl( p_org_id, -99 )
AND papl1.gl_period_name=p_gl_period
);
SELECT min(decode(p_epp_flag,'Y',papl.start_date,papl.end_date))
INTO l_rev_pa_date
FROM pa_periods_all papl
WHERE nvl(papl.org_id, -99 ) = nvl(p_org_id, -99 )
AND papl.gl_period_name = p_gl_period ;
SELECT period_name
INTO l_rev_pa_period_name
FROM pa_periods_all papl
WHERE nvl(papl.org_id, -99 ) = nvl(p_org_id, -99 )
AND trunc(l_rev_pa_date) between papl.start_date and papl.end_date;
pa_debug.g_err_stage := 'Before select get_rev_accrual_date() for ref dt-'||to_char(p_reference_date);
SELECT PERIOD.start_date,PERIOD.end_date
INTO l_org_accr_start_date,l_org_accr_end_date
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.application_id = p_application_id
AND PERIOD.set_of_books_id = p_set_of_books_id
AND PERIOD.adjustment_period_flag = 'N'
AND trunc(p_reference_date) BETWEEN PERIOD.start_date and PERIOD.end_date;
pa_debug.g_err_stage :='NDF - Prvdr GL Period SELECT';
SELECT PERIOD.start_date,PERIOD.end_date,PERIOD.closing_status,PERIOD.period_name
INTO l_rev_accr_nxt_st_dt,l_rev_accr_nxt_end_dt,l_period_status,l_period_name
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.application_id = p_application_id
AND PERIOD.set_of_books_id = p_set_of_books_id
AND PERIOD.adjustment_period_flag = 'N'
AND PERIOD.start_date = ( SELECT min(PERIOD.start_date)
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.application_id = p_application_id
AND PERIOD.set_of_books_id = p_set_of_books_id
AND PERIOD.adjustment_period_flag = 'N'
AND PERIOD.start_date > l_org_accr_end_date);
SELECT PERIOD.period_name,PERIOD.start_date,PERIOD.end_date,PERIOD.closing_status
INTO l_accr_gl_period_name, l_accr_gl_period_st_dt,l_accr_gl_period_end_dt,l_period_status
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.application_id = p_application_id
AND PERIOD.set_of_books_id = p_set_of_books_id
AND PERIOD.adjustment_period_flag = 'N'
AND trunc(p_reference_date) BETWEEN PERIOD.start_date and PERIOD.end_date;
select ts.allow_burden_flag
into l_allow_burden_flag
from pa_transaction_sources ts
where ts.transaction_source = p_transaction_source;
G_BdMethodProjID_Tab.Delete;
SELECT DECODE(pt.burden_amt_display_method, 'D', 'D'
, DECODE(pt.BURDEN_SUM_DEST_PROJECT_ID, NULL
, DECODE(pt.BURDEN_SUM_DEST_TASK_ID, NULL, 'S', 'D'), 'D'))
INTO x_burden_method
FROM pa_project_types_all pt
,pa_projects_all p
WHERE p.project_id = P_Project_Id
AND p.project_type = pt.project_type
AND pt.org_id = p.org_id
AND pt.burden_cost_flag = 'Y';
G_Bd_MethodProjID_Tab.Delete;
SELECT pt.burden_amt_display_method
INTO x_burden_method
FROM pa_project_types_all pt
,pa_projects_all p
WHERE p.project_id = P_Project_Id
AND p.project_type = pt.project_type
-- begin bug 5614790
-- AND NVL(pt.org_id,-99) = nvl(p.org_id,-99)
AND pt.org_id = p.org_id
-- end bug 5614790
AND pt.burden_cost_flag = 'Y';
G_CapCostTypProjID_Tab.Delete;
SELECT pt.capital_cost_type_code
INTO x_capital_cost_type
FROM pa_project_types_all pt
,pa_projects_all p
WHERE p.project_id = P_Project_Id
AND p.project_type = pt.project_type
-- begin bug 5614790
-- AND NVL(pt.org_id,-99) = nvl(p.org_id,-99)
AND pt.org_id = p.org_id
-- end bug 5614790
AND pt.project_type_class_code = 'CAPITAL';
SELECT imp.set_of_books_id
INTO l_set_of_books_id
FROM pa_implementations imp;
SELECT PERIOD.period_name,PERIOD.start_date,PERIOD.end_date,PERIOD.closing_status
INTO l_gl_period_name, l_gl_period_st_dt,l_gl_period_end_dt,l_period_status
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.application_id = l_application_id
AND PERIOD.set_of_books_id = l_set_of_books_id
AND PERIOD.adjustment_period_flag = 'N'
AND trunc(p_reference_date) BETWEEN PERIOD.start_date and PERIOD.end_date;
SELECT PERIOD.start_date,
PERIOD.end_date,
PERIOD.end_date,
PERIOD.period_name
INTO l_gl_period_st_dt,
l_gl_period_end_dt,
l_gl_dt,
l_gl_period_name
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.application_id = l_application_id
AND PERIOD.set_of_books_id = l_set_of_books_id
AND PERIOD.effective_period_num =
(SELECT min(PERIOD1.effective_period_num)
FROM GL_PERIOD_STATUSES PERIOD1
WHERE PERIOD1.application_id = l_application_id
AND PERIOD1.set_of_books_id = l_set_of_books_id
AND PERIOD1.closing_status||'' IN ('O','F')
AND PERIOD1.adjustment_period_flag = 'N'
AND PERIOD1.effective_period_num >=
(SELECT PERIOD2.effective_period_num
FROM GL_PERIOD_STATUSES PERIOD2,
GL_DATE_PERIOD_MAP DPM,
GL_SETS_OF_BOOKS SOB
WHERE SOB.set_of_books_id = l_set_of_books_id
AND DPM.period_set_name = SOB.period_set_name
AND DPM.period_type = SOB.accounted_period_type
AND trunc(DPM.accounting_date) = trunc(p_reference_date)
AND DPM.period_name = PERIOD2.period_name
AND PERIOD2.application_id = l_application_id
AND PERIOD2.set_of_books_id = l_set_of_books_id ))
AND PERIOD.End_Date >= TRUNC(p_reference_date)
AND PERIOD.set_of_books_id = l_set_of_books_id ;
SELECT PERIOD.start_date,
PERIOD.start_date,
PERIOD.end_date,
PERIOD.period_name
INTO l_gl_dt,
l_gl_period_st_dt,
l_gl_period_end_dt,
l_gl_period_name
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.application_id = l_application_id
AND PERIOD.set_of_books_id = l_set_of_books_id
AND PERIOD.effective_period_num =
(SELECT min(PERIOD1.effective_period_num)
FROM GL_PERIOD_STATUSES PERIOD1
WHERE PERIOD1.application_id = l_application_id
AND PERIOD1.set_of_books_id = l_set_of_books_id
AND PERIOD1.closing_status||'' IN ('O','F')
AND PERIOD1.adjustment_period_flag = 'N'
AND PERIOD1.effective_period_num >=
(SELECT PERIOD2.effective_period_num
FROM GL_PERIOD_STATUSES PERIOD2,
GL_DATE_PERIOD_MAP DPM,
GL_SETS_OF_BOOKS SOB
WHERE SOB.set_of_books_id = l_set_of_books_id
AND DPM.period_set_name = SOB.period_set_name
AND DPM.period_type = SOB.accounted_period_type
AND trunc(DPM.accounting_date) = trunc(p_reference_date)
AND DPM.period_name = PERIOD2.period_name
AND PERIOD2.application_id = l_application_id
AND PERIOD2.set_of_books_id = l_set_of_books_id ))
AND PERIOD.Start_Date > TRUNC(p_reference_date);
SELECT DISTINCT
e.expenditure_id,
e.incurred_by_person_id,
e.expenditure_ending_date
FROM pa_expenditure_items ei, pa_expenditures e
WHERE ei.expenditure_id = e.expenditure_id
AND ei.system_linkage_function IN ('ST','OT')
AND ei.Cost_Distributed_Flag = 'S'
AND e.person_type IS NULL
AND ei.request_id = req_id;
SELECT DISTINCT
e.expenditure_id,
e.incurred_by_person_id,
e.expenditure_ending_date
FROM pa_cost_distribution_lines cdl, pa_expenditures e,
pa_expenditure_items ei
WHERE TRUNC(CDL.GL_DATE) <= NVL(gl_date,TRUNC(CDL.GL_DATE))
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND cdl.Transfer_Status_Code IN ('P', 'R', 'X')
AND cdl.Line_Type = 'R'
AND cdl.request_id = req_id
AND ei.expenditure_id = e.expenditure_id
AND ei.system_linkage_function IN ('ST','OT')
AND e.person_type IS NULL;
SELECT DISTINCT
e.expenditure_id,
e.incurred_by_person_id,
e.expenditure_ending_date
FROM pa_cost_distribution_lines cdl, pa_expenditures e,
pa_expenditure_items ei
WHERE cdl.expenditure_item_id = ei.expenditure_item_id
AND cdl.Transfer_Status_Code = 'X'
AND cdl.Line_Type = 'R'
AND cdl.request_id = req_id
AND ei.expenditure_id = e.expenditure_id
AND ei.system_linkage_function IN ('ST','OT')
AND e.person_type IS NULL;
SELECT business_group_id
INTO l_business_group_id
FROM pa_implementations;
UPDATE pa_expenditures e
SET person_type = (
SELECT decode(a.assignment_type,'E','EMP','C','CWK')
FROM per_assignment_status_types s
,per_all_assignments_f a
WHERE a.job_id IS NOT NULL
AND a.primary_flag = 'Y'
AND rec.expenditure_ending_date BETWEEN trunc(a.effective_start_date)
AND trunc(a.effective_end_date)
AND a.person_id = rec.incurred_by_person_id
AND a.assignment_type in ('E','C')
/* Added for bug#14517534 */
AND s.per_system_status in ('ACTIVE_ASSIGN','ACTIVE_CWK','TERM_ASSIGN')
AND s.assignment_status_type_id = a.assignment_status_type_id
AND ((l_Cross_BG_Profile = 'N' AND l_business_group_id = a.business_group_id+0)
OR l_Cross_BG_Profile = 'Y')),
request_id = p_request_id
WHERE expenditure_id = rec.expenditure_id;
pa_debug.write_file('PA_UTILS2.populate_person_type: Person Type updated');
UPDATE pa_expenditures e
SET person_type = (
SELECT decode(a.assignment_type,'E','EMP','C','CWK')
FROM per_assignment_status_types s
,per_all_assignments_f a
WHERE a.job_id IS NOT NULL
AND a.primary_flag = 'Y'
AND rec1.expenditure_ending_date BETWEEN trunc(a.effective_start_date)
AND trunc(a.effective_end_date)
AND a.person_id = rec1.incurred_by_person_id
AND a.assignment_type in ('E','C')
/* Added for bug#14517534 */
AND s.per_system_status in ('ACTIVE_ASSIGN','ACTIVE_CWK','TERM_ASSIGN')
AND s.assignment_status_type_id = a.assignment_status_type_id
AND ((l_Cross_BG_Profile = 'N' AND l_business_group_id = a.business_group_id+0)
OR l_Cross_BG_Profile = 'Y')),
request_id = p_request_id
WHERE expenditure_id = rec1.expenditure_id;
pa_debug.write_file('PA_UTILS2.populate_person_type: Person Type updated');
UPDATE pa_expenditures e
SET person_type = (
SELECT decode(a.assignment_type,'E','EMP','C','CWK')
FROM per_assignment_status_types s
,per_all_assignments_f a
WHERE a.job_id IS NOT NULL
AND a.primary_flag = 'Y'
AND rec1.expenditure_ending_date BETWEEN trunc(a.effective_start_date)
AND trunc(a.effective_end_date)
AND a.person_id = rec1.incurred_by_person_id
/* Added for bug#14517534 */
AND a.assignment_type in ('E','C')
AND s.per_system_status in ('ACTIVE_ASSIGN','ACTIVE_CWK','TERM_ASSIGN')
AND s.assignment_status_type_id = a.assignment_status_type_id
AND ((l_Cross_BG_Profile = 'N' AND l_business_group_id = a.business_group_id+0)
OR l_Cross_BG_Profile = 'Y')),
request_id = p_request_id
WHERE expenditure_id = rec1.expenditure_id;
pa_debug.write_file('PA_UTILS2.populate_person_type: Person Type updated');