[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure update_gms_balance (x_project_id IN number,
x_award_id IN number,
x_mode IN varchar2,
ERRBUF OUT NOCOPY varchar2,
RETCODE OUT NOCOPY varchar2) is
-- x_baseline_flag = 'Y' process called to base line a budget
-- x_baseline_flag = 'N' process called to update balance
x_sob_id number;
select end_date from gms_balances
where budget_version_id = x_budget_version_id
for update nowait;
delete from gms_balances where budget_version_id = x_budget_version_id;
delete from gms_bc_packets
where project_id = x_project_id
and award_id = x_award_id
and budget_version_id = x_budget_version_id;
insert into gms_balances (
PROJECT_ID,
AWARD_ID,
TASK_ID,
TOP_TASK_ID,
RESOURCE_LIST_MEMBER_ID,
BALANCE_TYPE,
SET_OF_BOOKS_ID,
BUDGET_VERSION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
PERIOD_NAME,
START_DATE,
END_DATE,
PARENT_MEMBER_ID,
BUDGET_PERIOD_TO_DATE)
select
ga.project_id,
gv.award_id,
ga.task_id,
pt.top_task_id,
ga.resource_list_member_id,
'BGT',
x_set_of_books_id,
gv.budget_version_id,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
gb.PERIOD_NAME,
gb.START_DATE,
gb.END_DATE,
rm.PARENT_MEMBER_ID,
gb.burdened_cost --gb.raw_cost
from
gms_budget_lines gb,
gms_resource_assignments ga,
pa_tasks pt,
pa_resource_list_members rm,
gms_budget_versions gv
where gv.budget_version_id = x_budget_version_id
and ga.resource_assignment_id = gb.resource_assignment_id
and ga.task_id = pt.task_id (+)
and ga.budget_version_id = gv.budget_version_id
and rm.resource_list_member_id = ga.resource_list_member_id;
-- TO INSERT Commitments (Requisitions) Bug 2009836
-- ---------------------------------------------------------------
--
-- Bug : 3362016 Grants integrations with CWK and PO Services.
-- sub select added in the from clause to use PO encumbered amount api.
insert into gms_bc_packets (
packet_id,
set_of_books_id,
je_source_name,
je_category_name,
actual_flag,
period_name,
period_year,
period_num,
project_id,
task_id,
award_id,
status_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
entered_dr,
entered_cr,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
document_type,
document_header_id,
document_distribution_id,
transfered_flag,
account_type,
budget_version_id,
bc_packet_id,
burdenable_raw_cost,
vendor_id, -- Bug 2069132 ( RLMI Change)
expenditure_category, -- Bug 2069132 ( RLMI Change)
revenue_category, -- Bug 2069132 ( RLMI Change)
ind_compiled_set_id -- Bug 2387678 ( Performance Tuning )
)
select
x_packet_id,
x_sob_id,
'Purchasing',
'Requisitions',
'E',
vw.period_name,
vw.period_year,
vw.period_num,
vw.project_id,
vw.task_id,
vw.award_id ,
'P',
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
decode(sign(vw.amount), 1, vw.amount, 0),
decode(sign(vw.amount), -1,vw.amount, 0),
vw.expenditure_type,
vw.expenditure_organization_id,
vw.expenditure_item_date,
'REQ',
vw.requisition_header_id,
vw.distribution_id,
'Y',
'E',
x_budget_version_id,
gms_bc_packets_s.nextval,
vw.burdenable_raw_cost,
vw.vendor_id,
vw.expenditure_category,
vw.revenue_category_code,
vw.ind_compiled_set_id
FROM
( select gps.period_name,
gps.period_year,
gps.period_num,
adl.project_id,
adl.task_id,
adl.award_id ,
PO_INTG_DOCUMENT_FUNDS_GRP.get_active_encumbrance_func
('REQUISITION', RD.DISTRIBUTION_ID) amount,
rd.expenditure_type,
rd.expenditure_organization_id,
trunc(rd.expenditure_item_date) expenditure_item_date,
rd.distribution_id,
adl.burdenable_raw_cost,
pet.expenditure_category,
pet.revenue_category_code,
adl.ind_compiled_set_id,
rd.requisition_line_id,
rh.requisition_header_id,
rl.vendor_id
from po_req_distributions_all rd,
gms_award_distributions adl,
po_requisition_lines_all rl,
po_requisition_headers_all rh,
gl_period_statuses gps,
pa_expenditure_types pet
WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
AND NVL(RL.MODIFIED_BY_AGENT_FLAG,'N') = 'N'
AND RL.SOURCE_TYPE_CODE = 'VENDOR'
AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
AND RD.ENCUMBERED_FLAG = 'Y'
AND ADL.PROJECT_ID = X_PROJECT_ID
AND ADL.AWARD_ID = X_AWARD_Id
AND ADL.DISTRIBUTION_ID = RD.DISTRIBUTION_ID
AND ADL.ADL_STATUS = 'A'
AND ADL.DOCUMENT_TYPE = 'REQ'
AND NVL(ADL.FC_STATUS,'N') = 'A'
AND RD.PROJECT_ID = ADL.PROJECT_ID
AND RD.TASK_ID = ADL.TASK_ID
AND RD.AWARD_ID = ADL.AWARD_SET_ID
AND trunc(RD.EXPENDITURE_ITEM_DATE) BETWEEN trunc(GPS.START_DATE)
AND trunc(GPS.END_DATE) --Bug 9232992
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = X_SOB_ID
AND pet.expenditure_type = rd.expenditure_type
) VW
WHERE nvl(VW.amount,0) <> 0 ;
-- TO INSERT Commitments (Purchase Order) Bug 2009836
-- ---------------------------------------------------------------
--
-- Bug : 3362016 Grants integrations with CWK and PO Services.
-- sub select added in the from clause to use PO encumbered amount api.
insert into gms_bc_packets (
packet_id,
set_of_books_id,
je_source_name,
je_category_name,
actual_flag,
period_name,
period_year,
period_num,
project_id,
task_id,
award_id,
status_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
entered_dr,
entered_cr,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
document_type,
document_header_id,
document_distribution_id,
transfered_flag,
account_type,
budget_version_id,
bc_packet_id,
burdenable_raw_cost,
vendor_id, -- Bug 2069132 ( RLMI Change)
expenditure_category, -- Bug 2069132 ( RLMI Change)
revenue_category, -- Bug 2069132 ( RLMI Change)
ind_compiled_set_id -- Bug 2387678 ( Performance Tuning )
)
select
x_packet_id,
x_sob_id,
'Purchasing',
'Purchases',
'E',
vw.period_name,
vw.period_year,
vw.period_num,
vw.project_id,
vw.task_id,
vw.award_id,
'P',
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
vw.amount,
0, -- Entered_Cr
vw.expenditure_type,
vw.expenditure_organization_id,
vw.expenditure_item_date,
'PO',
vw.po_header_id,
vw.po_distribution_id,
'Y',
'E',
x_budget_version_id,
gms_bc_packets_s.nextval,
vw.burdenable_raw_cost,
vw.vendor_id, -- Bug 2069132 ( RLMI Change)
vw.expenditure_category, -- Bug 2069132 ( RLMI Change)
vw.revenue_category_code, -- Bug 2069132 ( RLMI Change)
vw.ind_compiled_set_id -- Bug 2387678 (Performance Tuning)
FROM (select
gps.period_name,
gps.period_year,
gps.period_num,
adl.project_id,
adl.task_id,
adl.award_id,
PO_INTG_DOCUMENT_FUNDS_GRP.get_active_encumbrance_func
('PO', pod.po_DISTRIBUTION_ID) amount,
pod.expenditure_type,
pod.expenditure_organization_id,
trunc(pod.expenditure_item_date) expenditure_item_date,
pod.po_header_id,
pod.po_distribution_id,
adl.burdenable_raw_cost,
poh.vendor_id,
pet.expenditure_category,
pet.revenue_category_code,
adl.ind_compiled_set_id
FROM
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_releases_all por,
po_distributions_all pod,
gms_award_distributions adl,
gl_period_statuses gps,
pa_expenditure_types pet
WHERE POH.TYPE_LOOKUP_CODE IN ('STANDARD','BLANKET','PLANNED')
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED','PLANNED')
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND PO_INTG_DOCUMENT_FUNDS_GRP.get_active_encumbrance_func /*Bug 6085276 */
('PO', pod.po_DISTRIBUTION_ID) <> 0
AND NVL(POH.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' /* 6085276 */
AND NVL(pll.closed_code,'OPEN') <> 'FINALLY CLOSED' /* 6085276 */
/*AND POD.ENCUMBERED_FLAG = 'Y' Commented for bug 6085276 */
AND POD.PROJECT_ID = X_PROJECT_ID
AND POD.DISTRIBUTION_TYPE <> 'PREPAYMENT' -- Complex work/subcontractor uptake
AND ADL.AWARD_ID = X_AWARD_Id
AND ADL.PROJECT_ID = POD.PROJECT_ID
AND ADL.PO_DISTRIBUTION_ID= POD.PO_DISTRIBUTION_ID
AND ADL.TASK_ID = POD.TASK_ID
AND POD.AWARD_ID = ADL.AWARD_SET_ID
AND ADL.ADL_STATUS = 'A'
AND ADL.DOCUMENT_TYPE = 'PO'
AND NVL(ADL.FC_STATUS,'N')= 'A'
AND trunc(POD.EXPENDITURE_ITEM_DATE) BETWEEN trunc(GPS.START_DATE)
AND trunc(GPS.END_DATE) --Bug 9232992
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = X_SOB_ID
AND pet.expenditure_type = pod.expenditure_type ) VW
WHERE NVL(VW.amount,0) <> 0 ;
-- TO INSERT Commitments (AP)
-- ---------------------------------------------------------------
-- ---------------------------------------------------------------
-- Bug Fix 2170878. Removed invoice_distribution_id join.In some
-- scenarios id is null on ad
-- ---------------------------------------------------------------
insert into gms_bc_packets (
packet_id,
set_of_books_id,
je_source_name,
je_category_name,
actual_flag,
period_name,
period_year,
period_num,
project_id,
task_id,
award_id,
status_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
entered_dr,
entered_cr,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
document_type,
document_header_id,
document_distribution_id,
TRANSFERED_FLAG,
account_type,
budget_version_id,
bc_packet_id,
burdenable_raw_cost,
vendor_id, -- Bug 2069132 ( RLMI Change)
expenditure_category, -- Bug 2069132 ( RLMI Change)
revenue_category, -- Bug 2069132 ( RLMI Change)
ind_compiled_set_id -- Bug 2387678 ( Performance Tuning )
)
select
x_packet_id,
x_sob_id,
'Payables', -- Bug 2603943
'Purchase Invoices',
'E',
gps.period_name,
gps.period_year,
gps.period_num,
adl.project_id,
adl.task_id,
adl.award_id,
'P',
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
-- Added below NVL clause as the base_amount stores correct amount in multi currency scenario
-- Bug 1980810 PA Rounding function added
pa_currency.round_currency_amt(decode(sign(pa_cmt_utils.get_apdist_amt( aid.invoice_distribution_id,
aid.invoice_id,
nvl(aid.base_amount,aid.amount),
'N', 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N'))),
1, pa_cmt_utils.get_apdist_amt( aid.invoice_distribution_id,
aid.invoice_id,
nvl(aid.base_amount,aid.amount),
'N', 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N')) ,
0)), -- Bug 2386531
pa_currency.round_currency_amt(decode(sign(pa_cmt_utils.get_apdist_amt( aid.invoice_distribution_id,
aid.invoice_id,
nvl(aid.base_amount,aid.amount),
'N', 'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') )),
-1,abs( pa_cmt_utils.get_apdist_amt( aid.invoice_distribution_id,
aid.invoice_id,
nvl(aid.base_amount,aid.amount),
'N', 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N') )),
0)),--Bug 2386531
aid.expenditure_type,
aid.expenditure_organization_id,
aid.expenditure_item_date,
'AP',
aid.invoice_id,
aid.invoice_distribution_id, -- AP Lines change
'Y',
'E',
x_budget_version_id,
gms_bc_packets_s.nextval,
adl.burdenable_raw_cost,
ap.vendor_id,
pet.expenditure_category,
pet.revenue_category_code,
adl.ind_compiled_set_id -- Bug 2387678 (Performance Tuning)
from ap_invoices_all ap,
gms_award_distributions adl,
ap_invoice_distributions aid,
gl_period_statuses gps,
pa_expenditure_types pet,
gl_ledgers g
where ap.invoice_id = aid.invoice_id
and aid.invoice_distribution_id = adl.invoice_distribution_id -- AP Lines change
and aid.invoice_id = adl.invoice_id
and adl.document_type = 'AP'
and adl.award_set_id = aid.award_id
and adl.adl_status = 'A'
and nvl(adl.fc_status,'N') = 'A'
and nvl(aid.pa_addition_flag,'N') = 'N'
and trunc(aid.expenditure_item_date)
between trunc(gps.start_date) and trunc(gps.end_date) --Bug 9232992
and gps.adjustment_period_flag = 'N'
and gps.application_id = 101
and gps.set_of_books_id = x_sob_id
and adl.project_id = x_project_id
and adl.award_id = x_award_id
and pa_cmt_utils.get_apdist_amt( aid.invoice_distribution_id,
aid.invoice_id,
nvl(aid.base_amount,aid.amount),
'N', 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N') ) <> 0
and nvl(aid.match_status_flag, 'X') = 'A'
and pet.expenditure_type = aid.expenditure_type
and g.ledger_id = aid.set_of_books_id ;
and not exists (select 'X'
from pa_cost_distribution_lines_all cdl
where cdl.system_reference2 = to_char(aid.invoice_id)
and cdl.system_reference3 = to_char(aid.distribution_line_number))
*/
exception
when no_data_found then
null;
-- The following insert staement should pick up following Scenarios :
-- Transactions interfaced from AP (these expenditures will have fc_status = 'A'
-- Expenditures having Funds check passed CDL which failed funds checking during Re-costing
-- As we are checking for fc_status on ADL , these lines will be picked up
-- -------------------------------------------------------------------------------------------------------
begin
-- ---------------------------------------------------------------
-- TO INSERT Expenditures and Encumberances
-- ---------------------------------------------------------------
insert into gms_bc_packets (
PACKET_ID,
PROJECT_ID,
AWARD_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
ACTUAL_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SET_OF_BOOKS_ID,
JE_CATEGORY_NAME,
JE_SOURCE_NAME,
TRANSFERED_FLAG,
DOCUMENT_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
DOCUMENT_HEADER_ID,
DOCUMENT_DISTRIBUTION_ID,
ACCOUNT_TYPE,
ENTERED_DR,
ENTERED_CR,
BUDGET_VERSION_ID,
bc_packet_id,
burdenable_raw_cost,
person_id, -- Bug 2069132 ( RLMI Change)
job_id, -- Bug 2069132 ( RLMI Change)
vendor_id, -- Bug 2069132 ( RLMI Change)
expenditure_category, -- Bug 2069132 ( RLMI Change)
revenue_category, -- Bug 2069132 ( RLMI Change)
ind_compiled_set_id -- Bug 2387678 ( Performance Tuning )
)
select
x_packet_id,
--task.project_id, -- commented for porting Bug:1703510
pc.project_id, -- added for above bug
adl.award_id,
pe.task_id,
pe.EXPENDITURE_TYPE,
trunc(pe.EXPENDITURE_ITEM_DATE),
'A',
'P',
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
x_sob_id,
DECODE(pe.system_linkage_function,'OT','Labor Cost',
'ST','Labor Cost',
'ER','Purchase Invoices',
'VI','Purchase Invoices',
'USG','Usage Cost',
'PJ','Miscellaneous Transaction',
'INV','Inventory',
'WIP','WIP'), -- Bug 2461450 : Replaced 'Expenditures' with DECODE statement
'Project Accounting',
'Y',
'EXP', -- for document_type
nvl(pe.override_to_organization_id,pa.incurred_by_organization_id),
gl.PERIOD_NAME,
gl.PERIOD_YEAR,
gl.PERIOD_NUM,
pc.expenditure_item_id,
pc.line_num,
'E',
decode(sign(pc.amount),1,pc.amount,0),
decode(sign(pc.amount),-1,ABS(pc.amount),0),
x_budget_version_id,
gms_bc_packets_s.nextval,
adl.burdenable_raw_cost,
pa.incurred_by_person_id, -- Bug 2069132 ( RLMI Change)
pe.job_id, -- Bug 2069132 ( RLMI Change)
pc.system_reference1, -- Bug 2069132 ( RLMI Change)
pet.expenditure_category, -- Bug 2069132 ( RLMI Change)
pet.revenue_category_code, -- Bug 2069132 ( RLMI Change)
adl.ind_compiled_set_id -- Bug 2387678 (Performance Tuning)
/* Changed the order of queries and
removed join with tables
gl_date_period_map map,
gl_sets_of_books glsob and pa_implementations imp for bug# 6043224 */
FROM GMS_AWARD_DISTRIBUTIONS ADL,
PA_COST_DISTRIBUTION_LINES_ALL PC,
GL_PERIOD_STATUSES GL,
PA_EXPENDITURE_ITEMS_ALL PE,
PA_EXPENDITURES_ALL PA,
PA_EXPENDITURE_TYPES PET
WHERE ADL.PROJECT_ID = x_project_id
AND ADL.AWARD_ID = x_award_id
AND ADL.ADL_STATUS = 'A'
AND NVL(ADL.FC_STATUS,'N') = 'A'
AND ADL.DOCUMENT_TYPE = 'EXP'
AND pc.expenditure_item_id = adl.expenditure_item_id
and pc.line_num = adl.cdl_line_num
AND PC.LINE_TYPE = 'R'
AND NVL(PC.AMOUNT,0) <> 0
AND GL.APPLICATION_ID = 101
AND GL.SET_OF_BOOKS_ID = x_sob_id
AND GL.ADJUSTMENT_PERIOD_FLAG = 'N'
AND trunc(PC.GL_DATE) BETWEEN GL.START_DATE AND GL.END_DATE -- Added trunc for bug 8458913
AND PE.EXPENDITURE_ITEM_ID = PC.EXPENDITURE_ITEM_ID
AND PE.EXPENDITURE_ITEM_ID = ADL.EXPENDITURE_ITEM_ID
AND PA.EXPENDITURE_ID = PE.EXPENDITURE_ID
AND PET.EXPENDITURE_TYPE = PE.EXPENDITURE_TYPE;
-- 2337127 ( Budget Baseline should insert all the cdls.
-- that has passed fundscheck previously..
-- and pe.cost_distributed_flag = 'Y' -- Bug 3283448 : Only check for fc_status , if fc_status = 'A' we should pick the record
and pet.expenditure_type = pe.expenditure_type -- Bug 2069132 (RLMI Change)
-- Added the following conditions for bug 5569067
AND map.period_set_name = glsob.Period_set_name
AND map.period_type = glsob.accounted_period_type
AND imp.org_id = pe.org_id
AND glsob.set_of_books_id = imp.set_of_books_id
AND map.accounting_date = trunc(pc.gl_date) -- Modified from pe.expenditure_item_date to pc.gl_date for the bug 5725787
Added trunc in the above condition for bug5960821
AND gl.period_name= map.period_name; Commented for Bug 6043224 */
-- Encumbrance Insert for Baseline
-- ---------------------------------------------------------------------------------------------
-- ---------------------------------------------------------------------------------------------
-- Bug Fix 2170878. Encumbrance insert should have condition of adl.document_type = 'ENC' as
-- both encumbrance_item_id and expenditure_item_id is stored in expenditure_item_id in adl table
-- ---------------------------------------------------------------------------------------------
begin
insert into gms_bc_packets (
PACKET_ID,
PROJECT_ID,
AWARD_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
ACTUAL_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SET_OF_BOOKS_ID,
JE_CATEGORY_NAME,
JE_SOURCE_NAME,
TRANSFERED_FLAG,
DOCUMENT_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
DOCUMENT_HEADER_ID,
DOCUMENT_DISTRIBUTION_ID,
ACCOUNT_TYPE,
ENTERED_DR,
ENTERED_CR,
BUDGET_VERSION_ID,
bc_packet_id,
burdenable_raw_cost,
person_id, -- Bug 2069132 ( RLMI Change)
job_id, -- Bug 2069132 ( RLMI Change)
expenditure_category, -- Bug 2069132 ( RLMI Change)
revenue_category, -- Bug 2069132 ( RLMI Change)
ind_compiled_set_id -- Bug 2387678 ( Performance Tuning )
)
select
x_packet_id,
--task.project_id, -- commented out NOCOPY for porting bug:1703510
adl.project_id, -- added for the above bug
adl.award_id,
gei.task_id,
gei.encumbrance_type,
trunc(gei.encumbrance_item_date),
'E',
'P',
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
x_sob_id,
'Encumbrances', -- Bug 2461450
'Project Accounting',
'Y',
'ENC', -- for document_type
nvl(gei.override_to_organization_id,ge.incurred_by_organization_id),
gl.PERIOD_NAME,
gl.PERIOD_YEAR,
gl.PERIOD_NUM,
gei.encumbrance_item_id,
adl.adl_line_num, --Bug 5726575 1,
'E',
-- Bug 1980810 PA Rounding function added
pa_currency.round_currency_amt(decode(sign(gei.amount),1,gei.amount,0)),
pa_currency.round_currency_amt(decode(sign(gei.amount),-1,-1*gei.amount,0)),
x_budget_version_id,
gms_bc_packets_s.nextval,
adl.burdenable_raw_cost,
ge.incurred_by_person_id, -- Bug 2069132 ( RLMI Change)
gei.job_id, -- Bug 2069132 ( RLMI Change)
pet.expenditure_category, -- Bug 2069132 ( RLMI Change)
pet.revenue_category_code, -- Bug 2069132 ( RLMI Change)
adl.ind_compiled_set_id -- Bug 2387678 (Performance Tuning)
from gms_encumbrance_items_all gei,
gms_encumbrances_all ge,
gl_period_STATUSES gl,
--pa_tasks task, -- commented out NOCOPY for porting bug:1703510
gms_award_distributions adl,
pa_expenditure_types pet -- Bug 2069132 (RLMI Change)
-- where task.project_id = x_project_id -- commented out NOCOPY for porting bug:1703510
-- and gei.task_id = task.task_id -- commented out NOCOPY for porting bug:1703510
where adl.project_id = x_project_id -- added for the above bug
and adl.award_id = x_award_id
-- and adl.project_id = gei.project_id -- commented out NOCOPY for Bug: 1666853
and adl.task_id = gei.task_id
and adl.adl_status = 'A'
and nvl(adl.fc_status,'N') = 'A'
and adl.document_type = 'ENC'
and ge.encumbrance_id = gei.encumbrance_id
-- and pp.end_date = gei.pa_date -- commented out NOCOPY for Bug: 1666853
and gei.encumbrance_item_date between gl.start_date and gl.end_date -- added for Bug: 1666853
-- and pp.gl_period_name = gl.period_name -- commented out NOCOPY for Bug: 1666853
and gl.application_id = 101
and gl.set_of_books_id = x_sob_id
and gl.adjustment_period_flag = 'N' ---> bug 3201867
and gei.encumbrance_item_id = adl.expenditure_item_id
and gei.enc_distributed_flag = 'Y'
and nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
and adl.line_num_reversed is null --Bug 5726575
and pet.expenditure_type = gei.encumbrance_type; -- Bug 2069132 (RLMI Change)
Insert into gms_bc_packets
( PACKET_ID,
PROJECT_ID,
AWARD_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
ACTUAL_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SET_OF_BOOKS_ID,
JE_CATEGORY_NAME,
JE_SOURCE_NAME,
TRANSFERED_FLAG,
DOCUMENT_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
DOCUMENT_HEADER_ID ,
DOCUMENT_DISTRIBUTION_ID,
TOP_TASK_ID,
BUDGET_VERSION_ID,
RESOURCE_LIST_MEMBER_ID,
ACCOUNT_TYPE,
ENTERED_DR,
ENTERED_CR ,
TOLERANCE_AMOUNT,
TOLERANCE_PERCENTAGE,
OVERRIDE_AMOUNT,
EFFECT_ON_FUNDS_CODE ,
RESULT_CODE,
GL_BC_PACKETS_ROWID,
BC_PACKET_ID,
PARENT_BC_PACKET_ID,
person_id, -- Bug 2069132 ( RLMI Change)
job_id, -- Bug 2069132 ( RLMI Change)
vendor_id, -- Bug 2069132 ( RLMI Change)
expenditure_category, -- Bug 2069132 ( RLMI Change)
revenue_category -- Bug 2069132 ( RLMI Change)
)
select /*+ index(gbc GMS_BC_PACKETS_N1) */ --Added the index hint for bug 5689194
gbc.PACKET_ID,
gbc.PROJECT_ID,
gbc.AWARD_ID,
gbc.TASK_ID,
icc.EXPENDITURE_TYPE, /* for performance fix bug 5569067 */ /* Bug 5676410 */
trunc(gbc.EXPENDITURE_ITEM_DATE),
gbc.ACTUAL_FLAG,
gbc.STATUS_CODE,
gbc.LAST_UPDATE_DATE,
gbc.LAST_UPDATED_BY,
gbc.CREATED_BY,
gbc.CREATION_DATE,
gbc.LAST_UPDATE_LOGIN,
gbc.SET_OF_BOOKS_ID,
gbc.JE_CATEGORY_NAME,
gbc.JE_SOURCE_NAME,
gbc.TRANSFERED_FLAG,
gbc.DOCUMENT_TYPE,
gbc.EXPENDITURE_ORGANIZATION_ID,
gbc.PERIOD_NAME,
gbc.PERIOD_YEAR,
gbc.PERIOD_NUM,
gbc.DOCUMENT_HEADER_ID ,
gbc.DOCUMENT_DISTRIBUTION_ID,
gbc.TOP_TASK_ID,
gbc.BUDGET_VERSION_ID,
gbc.RESOURCE_LIST_MEMBER_ID,
gbc.ACCOUNT_TYPE,
-- Bug 1980810 PA Rounding function added
pa_currency.round_currency_amt(sign(nvl(entered_dr,0)) * abs(nvl(gbc.BURDENABLE_RAW_COST ,0) * nvl(cm.compiled_multiplier,0))),
pa_currency.round_currency_amt(sign(nvl(entered_cr,0)) * abs(nvl(gbc.BURDENABLE_RAW_COST ,0) * nvl(cm.compiled_multiplier,0))),
gbc.TOLERANCE_AMOUNT,
gbc.TOLERANCE_PERCENTAGE,
gbc.OVERRIDE_AMOUNT,
gbc.EFFECT_ON_FUNDS_CODE ,
gbc.RESULT_CODE,
gbc.GL_BC_PACKETS_ROWID,
gms_bc_packets_s.nextval,
gbc.BC_PACKET_ID,
gbc.person_id, -- Bug 2069132 ( RLMI Change)
gbc.job_id, -- Bug 2069132 ( RLMI Change)
gbc.vendor_id, -- Bug 2069132 ( RLMI Change)
et.expenditure_category, -- Bug 2069132 ( RLMI Change)
et.revenue_category_code -- Bug 2069132 ( RLMI Change)
from /*pa_ind_rate_sch_revisions irsr, --for performance fix bug 5569067 */
--pa_cost_bases cb, --Bug 3630704 : Performance fix
pa_expenditure_types et,
pa_ind_cost_codes icc, /* Bug 5676410 */
pa_cost_base_exp_types cbet,
--pa_ind_rate_schedules_all_bg irs, --Bug 3630704 : Performance fix
pa_cost_base_cost_codes cbcc, /*for performance fix bug 5569067 */
/*pa_ind_compiled_sets ics, --for performance fix bug 5569067 */
pa_compiled_multipliers cm,
gms_bc_packets gbc
where gbc.document_type in ('REQ','PO','AP', 'ENC') -- perf bug 4005086. included 'ENC' here
and cbcc.cost_plus_structure = cbet.cost_plus_structure
/*and irsr.cost_plus_structure = cbet.cost_plus_structure bug 5569067 */
--and cb.cost_base = cbet.cost_base --Bug 3630704 : Performance fix
--and cb.cost_base_type = cbet.cost_base_type --Bug 3630704 : Performance fix
and et.expenditure_type = icc.expenditure_type /* Bug 5676410 */
and cbcc.cost_base = cbet.cost_base /*for performance fix bug 5569067 */
/*and ics.cost_base = cbet.cost_base -- Bug 3003584 */
and icc.ind_cost_code = cm.ind_cost_code /* Bug 5676410 */
and cbet.cost_base = cm.cost_base
and cm.cost_base_cost_code_id = cbcc.cost_base_cost_code_id /*--for performance fix bug 5569067*/
and cm.ind_cost_code = cbcc.ind_cost_code /*--for performance fix bug 5569067*/
and cbet.cost_base_type = 'INDIRECT COST'
and cbet.expenditure_type = gbc.expenditure_type
--and irs.ind_rate_sch_id = irsr.ind_rate_sch_id --Bug 3630704 : Performance fix
/*and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id --for performance fix bug 5569067
and ics.ind_compiled_set_id = gbc.ind_compiled_set_id -- Replaced the above clause with this for Bug:2387678 --for performance fix bug 5569067
and ics.organization_id = gbc.expenditure_organization_id */
and cm.ind_compiled_set_id = gbc.ind_compiled_set_id
and cm.compiled_multiplier <> 0
and gbc.packet_id = x_packet_id;
Insert into gms_bc_packets
( PACKET_ID,
PROJECT_ID,
AWARD_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
ACTUAL_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SET_OF_BOOKS_ID,
JE_CATEGORY_NAME,
JE_SOURCE_NAME,
TRANSFERED_FLAG,
DOCUMENT_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
DOCUMENT_HEADER_ID ,
DOCUMENT_DISTRIBUTION_ID,
TOP_TASK_ID,
BUDGET_VERSION_ID,
RESOURCE_LIST_MEMBER_ID,
ACCOUNT_TYPE,
ENTERED_DR,
ENTERED_CR ,
TOLERANCE_AMOUNT,
TOLERANCE_PERCENTAGE,
OVERRIDE_AMOUNT,
EFFECT_ON_FUNDS_CODE ,
RESULT_CODE,
GL_BC_PACKETS_ROWID,
BC_PACKET_ID,
PARENT_BC_PACKET_ID,
person_id, -- Bug 2069132 ( RLMI Change)
job_id, -- Bug 2069132 ( RLMI Change)
vendor_id, -- Bug 2069132 ( RLMI Change)
expenditure_category, -- Bug 2069132 ( RLMI Change)
revenue_category -- Bug 2069132 ( RLMI Change)
)
select /*+ index(gbc GMS_BC_PACKETS_N1) */ --Added the index hint for bug 5689194
gbc.PACKET_ID,
gbc.PROJECT_ID,
gbc.AWARD_ID,
gbc.TASK_ID,
icc.EXPENDITURE_TYPE,
trunc(gbc.EXPENDITURE_ITEM_DATE),
gbc.ACTUAL_FLAG,
gbc.STATUS_CODE,
gbc.LAST_UPDATE_DATE,
gbc.LAST_UPDATED_BY,
gbc.CREATED_BY,
gbc.CREATION_DATE,
gbc.LAST_UPDATE_LOGIN,
gbc.SET_OF_BOOKS_ID,
gbc.JE_CATEGORY_NAME,
gbc.JE_SOURCE_NAME,
gbc.TRANSFERED_FLAG,
gbc.DOCUMENT_TYPE,
gbc.EXPENDITURE_ORGANIZATION_ID,
gbc.PERIOD_NAME,
gbc.PERIOD_YEAR,
gbc.PERIOD_NUM,
gbc.DOCUMENT_HEADER_ID ,
gbc.DOCUMENT_DISTRIBUTION_ID,
gbc.TOP_TASK_ID,
gbc.BUDGET_VERSION_ID,
gbc.RESOURCE_LIST_MEMBER_ID,
gbc.ACCOUNT_TYPE,
-- Bug 1980810 PA Rounding function added
pa_currency.round_currency_amt(decode(nvl(entered_dr,0),0,0,(abs(nvl(gbc.BURDENABLE_RAW_COST ,0)) * nvl(cm.compiled_multiplier,0)))),
pa_currency.round_currency_amt(decode(nvl(entered_cr,0),0,0,(abs(nvl(gbc.BURDENABLE_RAW_COST ,0)) * nvl(cm.compiled_multiplier,0)))),
gbc.TOLERANCE_AMOUNT,
gbc.TOLERANCE_PERCENTAGE,
gbc.OVERRIDE_AMOUNT,
gbc.EFFECT_ON_FUNDS_CODE ,
gbc.RESULT_CODE,
gbc.GL_BC_PACKETS_ROWID,
gms_bc_packets_s.nextval,
gbc.BC_PACKET_ID,
gbc.person_id, -- Bug 2069132 ( RLMI Change)
gbc.job_id, -- Bug 2069132 ( RLMI Change)
gbc.vendor_id, -- Bug 2069132 ( RLMI Change)
et.expenditure_category, -- Bug 2069132 ( RLMI Change)
et.revenue_category_code -- Bug 2069132 ( RLMI Change)
from --pa_ind_rate_sch_revisions irsr, /* Commented for bug 5689194 */
--pa_cost_bases cb, --Bug 3630704 : Performance fix
pa_expenditure_types et,
pa_ind_cost_codes icc,
pa_cost_base_exp_types cbet,
--pa_ind_rate_schedules_all_bg irs, --Bug 3630704 : Performance fix
pa_cost_base_cost_codes cbcc , /* added for bug 5689194 */
--pa_ind_compiled_sets ics, /* commented for bug 5689194 */
pa_compiled_multipliers cm,
pa_expenditure_items_all ei, --Bug Fix 1482377
pa_transaction_sources pts, --Bug Fix 1482377
gms_bc_packets gbc
where gbc.document_type = 'EXP'
and cbcc.cost_plus_structure = cbet.cost_plus_structure -- Bug 5689194
--and cb.cost_base = cbet.cost_base --Bug 3630704 : Performance fix
--and cb.cost_base_type = cbet.cost_base_type --Bug 3630704 : Performance fix
--and ics.cost_base = cbet.cost_base -- 3003584 Bug 5689194
and cbcc.cost_base = cbet.cost_base -- Bug 5689194
and et.expenditure_type = icc.expenditure_type
and icc.ind_cost_code = cm.ind_cost_code
and cbet.cost_base = cm.cost_base
and cbet.cost_base_type = 'INDIRECT COST'
and cm.cost_base_cost_code_id = cbcc.cost_base_cost_code_id --Bug 5689194
and cm.ind_cost_code = cbcc.ind_cost_code --Bug 5689194
and cbet.expenditure_type = gbc.expenditure_type
--and irs.ind_rate_sch_id = irsr.ind_rate_sch_id --Bug 3630704 : Performance fix
--and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id Bug 5689194
and gbc.document_type = 'EXP'
--and ics.ind_compiled_set_id = gbc.ind_compiled_set_id Bug 5689194
and cm.ind_compiled_set_id = gbc.ind_compiled_set_id
--and ics.organization_id = gbc.expenditure_organization_id Bug 5689194
and cm.compiled_multiplier <> 0
and ei.expenditure_item_id = gbc.document_header_id --Bug Fix 1482377
and (ei.transaction_source = pts.transaction_source (+) --Bug Fix 1482377
and nvl(pts.allow_burden_flag,'N') = 'N') --Bug Fix 1815635
and gbc.packet_id = x_packet_id;
Insert into gms_bc_packets
( PACKET_ID,
PROJECT_ID,
AWARD_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
ACTUAL_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SET_OF_BOOKS_ID,
JE_CATEGORY_NAME,
JE_SOURCE_NAME,
TRANSFERED_FLAG,
DOCUMENT_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
DOCUMENT_HEADER_ID ,
DOCUMENT_DISTRIBUTION_ID,
TOP_TASK_ID,
BUDGET_VERSION_ID,
RESOURCE_LIST_MEMBER_ID,
ACCOUNT_TYPE,
ENTERED_DR,
ENTERED_CR ,
TOLERANCE_AMOUNT,
TOLERANCE_PERCENTAGE,
OVERRIDE_AMOUNT,
EFFECT_ON_FUNDS_CODE ,
RESULT_CODE,
GL_BC_PACKETS_ROWID,
BC_PACKET_ID,
PARENT_BC_PACKET_ID,
person_id, -- Bug 2069132 ( RLMI Change)
job_id, -- Bug 2069132 ( RLMI Change)
vendor_id, -- Bug 2069132 ( RLMI Change)
expenditure_category, -- Bug 2069132 ( RLMI Change)
revenue_category -- Bug 2069132 ( RLMI Change)
)
select
gbc.PACKET_ID,
gbc.PROJECT_ID,
gbc.AWARD_ID,
gbc.TASK_ID,
icc.EXPENDITURE_TYPE,
trunc(gbc.EXPENDITURE_ITEM_DATE),
gbc.ACTUAL_FLAG,
gbc.STATUS_CODE,
gbc.LAST_UPDATE_DATE,
gbc.LAST_UPDATED_BY,
gbc.CREATED_BY,
gbc.CREATION_DATE,
gbc.LAST_UPDATE_LOGIN,
gbc.SET_OF_BOOKS_ID,
gbc.JE_CATEGORY_NAME,
gbc.JE_SOURCE_NAME,
gbc.TRANSFERED_FLAG,
gbc.DOCUMENT_TYPE,
gbc.EXPENDITURE_ORGANIZATION_ID,
gbc.PERIOD_NAME,
gbc.PERIOD_YEAR,
gbc.PERIOD_NUM,
gbc.DOCUMENT_HEADER_ID ,
gbc.DOCUMENT_DISTRIBUTION_ID,
gbc.TOP_TASK_ID,
gbc.BUDGET_VERSION_ID,
gbc.RESOURCE_LIST_MEMBER_ID,
gbc.ACCOUNT_TYPE,
-- Bug 1980810 PA Rounding function added
pa_currency.round_currency_amt(decode(nvl(entered_dr,0),0,0,(abs(nvl(gbc.BURDENABLE_RAW_COST ,0)) * nvl(cm.compiled_multiplier,0)))),
pa_currency.round_currency_amt(decode(nvl(entered_cr,0),0,0,(abs(nvl(gbc.BURDENABLE_RAW_COST ,0)) * nvl(cm.compiled_multiplier,0)))),
gbc.TOLERANCE_AMOUNT,
gbc.TOLERANCE_PERCENTAGE,
gbc.OVERRIDE_AMOUNT,
gbc.EFFECT_ON_FUNDS_CODE ,
gbc.RESULT_CODE,
gbc.GL_BC_PACKETS_ROWID,
gms_bc_packets_s.nextval,
gbc.BC_PACKET_ID,
gbc.person_id, -- Bug 2069132 ( RLMI Change)
gbc.job_id, -- Bug 2069132 ( RLMI Change)
gbc.vendor_id, -- Bug 2069132 ( RLMI Change)
et.expenditure_category, -- Bug 2069132 ( RLMI Change)
et.revenue_category_code -- Bug 2069132 ( RLMI Change)
from pa_ind_rate_sch_revisions irsr,
--pa_cost_bases cb, --Bug 3630704 : Performance fix
pa_expenditure_types et,
pa_ind_cost_codes icc,
pa_cost_base_exp_types cbet,
--pa_ind_rate_schedules_all_bg irs, --Bug 3630704 : Performance fix
pa_ind_compiled_sets ics,
pa_compiled_multipliers cm,
gms_bc_packets gbc
where irsr.cost_plus_structure = cbet.cost_plus_structure
--and cb.cost_base = cbet.cost_base --Bug 3630704 : Performance fix
--and cb.cost_base_type = cbet.cost_base_type --Bug 3630704 : Performance fix
and ics.cost_base = cbet.cost_base --Bug 3003584
and et.expenditure_type = icc.expenditure_type
and icc.ind_cost_code = cm.ind_cost_code
and cbet.cost_base = cm.cost_base
and cbet.cost_base_type = 'INDIRECT COST'
and cbet.expenditure_type = gbc.expenditure_type
--and irs.ind_rate_sch_id = irsr.ind_rate_sch_id --Bug 3630704 : Performance fix
and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
and gbc.document_type = 'ENC'
and ics.ind_compiled_set_id = gbc.ind_compiled_set_id -- Replaced the above clause with this for Bug:2387678
and cm.ind_compiled_set_id = gbc.ind_compiled_set_id
and ics.organization_id = gbc.expenditure_organization_id
and cm.compiled_multiplier <> 0 -- Fix for Bug 806481
and gbc.packet_id = x_packet_id;
SELECT 'X'
FROM pa_child_resources_v a,
pa_expenditure_types b,
pa_resource_lists c
WHERE a.resource_list_id = x_res_list_id
AND a.resource_type_name = 'Expenditure Category'
AND a.resource_list_id = c.resource_list_id
AND c.group_resource_type_id = 0
AND a.resource_name = b.expenditure_category
AND b.expenditure_type = x_expenditure_type
AND NVL(a.migration_code,'M') ='M'; -- Bug 3626671;
Cursor cur_update_col is
select /*+ index(gms GMS_BC_PACKETS_N1) */ gms.bc_packet_id, -- added the index hint for bug 5689194
gms.project_id,
gms.award_id,
gms.task_id,
gms.expenditure_organization_id,
gms.expenditure_type,
gms.document_type,
nvl(ei.system_linkage_function,'VI'),
TYPE.expenditure_category,
TYPE.revenue_category_code,
gms.award_id,
gms.parent_bc_packet_id,
pm.categorization_code, -- to calculate the correct rlmi if budget without resource
decode(sign(nvl(gms.entered_dr,0) - nvl(gms.entered_cr,0)),1,'D','I')
from gms_bc_packets gms,
gms_budget_versions bv,
pa_budget_entry_methods pm,
pa_expenditure_types TYPE,
gms_encumbrance_items_all ei
where gms.packet_id = x_packet_id
and gms.budget_version_id = bv.budget_version_id
and gms.document_type = 'ENC'
and bv.budget_entry_method_code = pm.budget_entry_method_code
and gms.expenditure_type = TYPE.expenditure_type
and gms.document_header_id = ei.encumbrance_item_id
union all
select /*+ index(gms GMS_BC_PACKETS_N1) */ gms.bc_packet_id, -- added the index hint for bug 5689194
gms.project_id,
gms.award_id,
gms.task_id,
gms.expenditure_organization_id,
gms.expenditure_type,
gms.document_type,
nvl(ei.system_linkage_function,'VI'),
TYPE.expenditure_category,
TYPE.revenue_category_code,
gms.award_id,
gms.parent_bc_packet_id,
pm.categorization_code, -- to calculate the correct rlmi if budget without resource
decode(sign(nvl(gms.entered_dr,0) - nvl(gms.entered_cr,0)),1,'D','I')
from gms_bc_packets gms,
gms_budget_versions bv,
pa_budget_entry_methods pm,
pa_expenditure_types TYPE,
pa_expenditure_items_all ei
where gms.packet_id = x_packet_id
and gms.budget_version_id = bv.budget_version_id
and gms.document_type = 'EXP'
and bv.budget_entry_method_code = pm.budget_entry_method_code
and gms.expenditure_type = TYPE.expenditure_type
and gms.document_header_id = ei.expenditure_item_id(+);
OPEN cur_update_col;
fetch cur_update_col into x_bc_packet_id,
x_project_id,
x_award_id,
x_task_id,
x_organization_id,
x_expenditure_type,
x_document_type,
x_system_linkage,
x_expenditure_category,
x_revenue_category,
x_awd_id,
x_parent_id,
x_categorization_code,
l_effect_on_funds_code;
exit when cur_update_col%notfound;
select bv.resource_list_id into x_res_list_id
from gms_budget_versions bv
where bv.budget_version_id = x_budget_version_id;
SELECT DISTINCT line.vendor_id
INTO x_vendor_id
FROM po_requisition_lines line,
po_requisition_headers req,
gms_bc_packets bc
WHERE bc.packet_id = x_packet_id
AND line.requisition_header_id = req.requisition_header_id
AND bc.document_header_id = req.requisition_header_id
AND bc.bc_packet_id = x_bc_packet_id;
SELECT DISTINCT head.vendor_id
INTO x_vendor_id
FROM po_headers_all head,
gms_bc_packets bc
WHERE bc.packet_id = x_packet_id
AND bc.document_header_id = head.po_header_id
AND bc.bc_packet_id = x_bc_packet_id;
SELECT DISTINCT head.vendor_id
INTO x_vendor_id
FROM ap_invoices_all head,
gms_bc_packets bc
WHERE bc.packet_id = x_packet_id
AND bc.document_header_id = head.invoice_id
AND bc.bc_packet_id = x_bc_packet_id;
SELECT DISTINCT EXP.incurred_by_person_id,
item.job_id
INTO x_person_id,
x_job_id
FROM pa_expenditures_all exp,
pa_expenditure_items_all item,
gms_bc_packets bc
WHERE bc.packet_id = x_packet_id
AND bc.bc_packet_id = x_bc_packet_id
AND bc.document_header_id = item.expenditure_item_id
AND item.expenditure_id = EXP.expenditure_id;
SELECT DISTINCT enc.incurred_by_person_id,
item.job_id
INTO x_person_id,
x_job_id
FROM gms_encumbrances_all enc,
gms_encumbrance_items_all item,
gms_bc_packets bc
WHERE bc.packet_id = x_packet_id
AND bc.bc_packet_id = x_bc_packet_id
AND bc.document_header_id = item.encumbrance_item_id
AND item.encumbrance_id = enc.encumbrance_id;
SELECT DISTINCT tp.attribute2,
tp.attribute3
INTO x_non_labor_resource,
x_non_labor_resource_org_id
FROM pa_expenditure_types tp,
gms_bc_packets bc
WHERE bc.packet_id = x_packet_id
AND bc.bc_packet_id = x_bc_packet_id
AND tp.expenditure_type = bc.expenditure_type;
SELECT DISTINCT EXP.incurred_by_person_id,
item.job_id,
item.non_labor_resource,
item.organization_id
INTO x_person_id,
x_job_id,
x_non_labor_resource,
x_non_labor_resource_org_id
FROM pa_expenditures_all exp,
pa_expenditure_items_all item,
gms_bc_packets bc
WHERE bc.packet_id = x_packet_id
AND bc.bc_packet_id = x_bc_packet_id
AND bc.document_header_id = item.expenditure_item_id
AND item.expenditure_id = EXP.expenditure_id;
UPDATE gms_bc_packets
SET status_code = 'R',
result_code = 'F94',
res_result_code = 'F94',
res_grp_result_code = 'F94',
task_result_code = 'F94',
top_task_result_code = 'F94',
award_result_code = 'F94'
WHERE packet_id = x_packet_id
AND bc_packet_id = x_bc_packet_id;
UPDATE gms_bc_packets
SET resource_list_member_id = x_resource_list_member_id,
effect_on_funds_code = l_effect_on_funds_code
WHERE packet_id = x_packet_id
AND bc_packet_id = x_bc_packet_id
AND budget_version_id = x_budget_version_id;
SELECT resource_list_member_id
INTO x_resource_list_member_id
FROM gms_balances gb
WHERE gb.budget_version_id = x_budget_version_id
AND balance_type = 'BGT'
AND ROWNUM = 1;
UPDATE gms_bc_packets
SET status_code = 'R',
result_code = 'F94',
res_result_code = 'F94',
res_grp_result_code = 'F94',
task_result_code = 'F94',
top_task_result_code = 'F94',
award_result_code = 'F94'
WHERE packet_id = x_packet_id
AND bc_packet_id = x_bc_packet_id;
UPDATE gms_bc_packets
SET resource_list_member_id = x_resource_list_member_id,
effect_on_funds_code = l_effect_on_funds_code
WHERE packet_id = x_packet_id
AND bc_packet_id = x_bc_packet_id
AND budget_version_id = x_budget_version_id;
Update gms_bc_packets
set status_code = 'T',
result_code = 'F82',
res_result_code = 'F82',
res_grp_result_code = 'F82',
task_result_code = 'F82',
top_task_result_code = 'F82',
award_result_code = 'F82'
where packet_id= x_packet_id
and bc_packet_id = x_bc_packet_id;
CLOSE cur_update_col;
Update gms_bc_packets
set status_code = 'T',
result_code = 'F100',
res_result_code = 'F100',
res_grp_result_code = 'F100',
task_result_code = 'F100',
top_task_result_code = 'F100',
award_result_code = 'F100'
where packet_id= x_packet_id;
if cur_update_col%ISOPEN then
close cur_update_col;
procedure update_bc_packet_status(x_packet_id in number) is
begin
update gms_bc_packets
set status_code = 'A'
where packet_id = x_packet_id
and status_code = 'P';
DELETE FROM gms_balances WHERE budget_version_id = x_budget_version_id;
DELETE FROM gms_bc_packets WHERE budget_version_id = x_budget_version_id;
INSERT INTO gms_balances (
PROJECT_ID,
AWARD_ID,
TASK_ID,
RESOURCE_LIST_MEMBER_ID,
SET_OF_BOOKS_ID,
BUDGET_VERSION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
PERIOD_NAME,
START_DATE,
END_DATE,
PARENT_MEMBER_ID,
BUDGET_PERIOD_TO_DATE,
ACTUAL_PERIOD_TO_DATE,
ENCUMB_PERIOD_TO_DATE)
SELECT
gms.project_id,
gms.award_id,
gms.task_id,
gms.resource_list_member_id,
gms.set_of_books_id,
x_budget_version_id,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
gms.PERIOD_NAME,
gms.START_DATE,
gms.END_DATE,
gms.PARENT_MEMBER_ID,
0,
gms.actual_period_to_date,
gms.encumb_period_to_date
FROM gms_balances gms
WHERE gms.budget_version_id = x_base_budget_version_id
AND ( NVL(gms.actual_period_to_date,0) <> 0 OR NVL(gms.encumb_period_to_date,0) <> 0 );
SELECT packet_id
FROM gms_bc_packets
WHERE budget_version_id = p_budget_version_id;
update_bc_packet_status(get_pacid_cur_var.packet_id);
|******** Procedure to update GMS_BALANCES table when funds check not reqd. ***|
+******************************************************************************/
PROCEDURE update_gms_fck_nr(x_budget_version_id number,
x_base_bud_version_id number,
x_sob_id number) IS
bud_amount number;
SELECT
ra.project_id,
gbv.award_id,
ra.task_id,
ra.resource_list_member_id,
gbv.budget_version_id,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
gbl.PERIOD_NAME,
gbl.START_DATE,
gbl.END_DATE,
rm.PARENT_MEMBER_ID,
gbl.burdened_cost --pb.raw_cost
FROM
gms_budget_lines gbl,
pa_resource_assignments ra,
gms_budget_versions gbv,
pa_resource_list_members rm
WHERE
gbv.budget_version_id = p_budget_version_id
and ra.resource_assignment_id = gbl.resource_assignment_id
and ra.budget_version_id = gbv.budget_version_id
and rm.resource_list_member_id = ra.resource_list_member_id;
SELECT budget_period_to_date
INTO bud_amount
FROM gms_balances
WHERE project_id = sel_rec.project_id
AND award_id = sel_rec.award_id
AND task_id = sel_rec.task_id
AND resource_list_member_id = sel_rec.resource_list_member_id
AND set_of_books_id = x_sob_id
AND budget_version_id = x_budget_version_id
AND start_date = sel_rec.start_date;
INSERT INTO gms_balances (project_id
,award_id
,task_id
,resource_list_member_id
,set_of_books_id
,budget_Version_id
,last_update_date
,last_updated_by
,created_by
,creation_date
,last_update_login
,period_name
,start_date
,end_date
,balance_type
,parent_member_id
,budget_period_to_date
)
VALUES
(sel_rec.project_id
,sel_rec.award_id
,sel_rec.task_id
,sel_rec.resource_list_member_id
,x_sob_id
,x_budget_version_id
,sysdate
,FND_GLOBAL.USER_ID
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.LOGIN_ID
,sel_rec.period_name
,sel_rec.start_date
,sel_rec.end_date
,'BGT'
,sel_rec.parent_member_id
,sel_rec.burdened_cost
);
UPDATE gms_balances
SET budget_period_to_date = sel_rec.burdened_cost
WHERE Project_id = sel_rec.project_id
AND award_id = sel_rec.award_id
AND task_id = sel_rec.task_id
AND resource_list_member_id = sel_rec.resource_list_member_id
AND set_of_books_id = x_sob_id
AND budget_version_id = x_budget_version_id
AND start_date = sel_rec.start_date;
END update_gms_fck_nr;
select set_of_books_id into x_sob_id
from pa_implementations;
select max(budget_version_id) into x_budget_version_id
from gms_budget_versions
where project_id = x_project_id
and award_id = to_char(x_award_id)
and ((budget_status_code ='W' and x_mode='S')
or (budget_status_code = 'B' and x_mode = 'B'));
select fc_required_flag
into x_fc_required_flag
from gms_budget_versions
where project_id = x_project_id
and award_id = x_award_id
and budget_status_code in ('W','S');
SELECT budget_version_id
INTO x_base_bud_version_id
FROM gms_budget_versions
WHERE project_id = x_project_id
AND award_id = x_award_id
AND budget_status_code = 'B'
AND (current_flag = 'Y'
OR current_flag = 'R');
SELECT 1 into x_dummy from dual
where exists (select 'x'
FROM gms_bc_packets
WHERE project_id = x_project_id
AND award_id = x_award_id
AND budget_version_id = x_base_bud_version_id
AND status_code ='A');
SELECT count(1)
INTO x_dummy
FROM gms_balances
WHERE project_id = x_project_id
AND award_id = x_award_id
AND budget_version_id = x_base_bud_version_id
AND actual_period_to_date is NOT NULL
AND encumb_period_to_date is NOT NULL;
update_gms_fck_nr(x_budget_version_id,x_base_bud_version_id,x_sob_id);
update_gms_fck_nr(x_budget_version_id,x_base_bud_version_id,x_sob_id);
select gl_bc_packets_s.nextval into x_packet_id from dual;
select count(packet_id) into x_run
from gms_bc_packets
where packet_id = x_packet_id
and rownum < 2;
-- Update resource list
RETCODE := 'S';
update_bc_packet_status(x_packet_id);
-- Update gms_balances using sweeper process
end if;
end update_gms_balance;
SELECT nvl(sum(aid.quantity_invoiced),0)
FROM ap_invoice_distributions aid,
gms_award_distributions adl
WHERE aid.po_distribution_id = p_po_distribution_id
AND aid.distribution_line_number = adl.distribution_line_number
AND aid.invoice_distribution_id = adl.invoice_distribution_id -- AP Lines change
AND aid.invoice_id = adl.invoice_id
AND adl.document_type = 'AP'
AND adl.award_set_id = aid.award_id
AND aid.line_type_lookup_code = 'ITEM'
AND adl.adl_status = 'A'
AND nvl(adl.fc_status,'N') = 'A'
AND nvl(aid.match_status_flag,'N') = 'A';