[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
into l_budget_control_flag
FROM DUAL
WHERE EXISTS ( select null
from pa_budgetary_control_options pbct
,pa_budget_types bv
where pbct.project_id = p_project_id
AND pbct.BDGT_CNTRL_FLAG = 'Y'
AND pbct.BUDGET_TYPE_CODE = bv.budget_type_code
AND Nvl(pbct.EXTERNAL_BUDGET_CODE,'GL') = p_ext_budget_code
AND bv.budget_amount_code = 'C'
);
/* Added budget_amount_code = 'C' to select cost budget only */
SELECT max(pbv.budget_version_id)
INTO l_budget_version_id
FROM pa_budget_versions pbv
,pa_budget_types bdgttype
,pa_budgetary_control_options pbct
WHERE pbv.project_id = p_project_id
AND pbv.current_flag = 'Y'
AND pbv.budget_status_code = 'B'
AND bdgttype.budget_type_code = pbv.budget_type_code
AND bdgttype.budget_amount_code = 'C'
AND pbct.project_id = pbv.project_id
AND pbct.BDGT_CNTRL_FLAG = 'Y'
AND pbct.BUDGET_TYPE_CODE = pbv.budget_type_code
AND NVL(pbct.EXTERNAL_BUDGET_CODE,'GL') = p_ext_budget_code ;
SELECT 'Y'
INTO l_return_var
FROM pa_implementations;
/** This is an autonmous Transaction API, which inserts records into
* pa_bc_packets. If the operation is success ,x_return_status will be set to 'S'
* else it will be set to 'T' - for fatal error and x_error_msg will return the sqlcode and sqlerrm
**/
PROCEDURE Load_pkts(
p_calling_module IN varchar2 default 'CCTRXIMPORT'
,p_ext_budget_type IN varchar2 default 'GL'
, p_packet_id IN number
, p_fc_rec_tab IN PA_CC_ENC_IMPORT_FCK.FC_Rec_Table
, x_return_status OUT NOCOPY varchar2
, x_error_msg OUT NOCOPY varchar2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
-- Note: We cannot use Bulk insert due to table of records
--PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
INSERT INTO PA_BC_PACKETS
(PACKET_ID
,BC_PACKET_ID
,PARENT_BC_PACKET_ID
,BC_COMMITMENT_ID
,PROJECT_ID
,TASK_ID
,EXPENDITURE_TYPE
,EXPENDITURE_ITEM_DATE
,SET_OF_BOOKS_ID
,JE_CATEGORY_NAME
,JE_SOURCE_NAME
,STATUS_CODE
,DOCUMENT_TYPE
,FUNDS_PROCESS_MODE
,EXPENDITURE_ORGANIZATION_ID
,DOCUMENT_HEADER_ID
,DOCUMENT_DISTRIBUTION_ID
,BUDGET_VERSION_ID
,BURDEN_COST_FLAG
,BALANCE_POSTED_FLAG
,ACTUAL_FLAG
,GL_DATE
,PERIOD_NAME
,PERIOD_YEAR
,PERIOD_NUM
,ENCUMBRANCE_TYPE_ID
,PROJ_ENCUMBRANCE_TYPE_ID
,TOP_TASK_ID
,PARENT_RESOURCE_ID
,RESOURCE_LIST_MEMBER_ID
,ENTERED_DR
,ENTERED_CR
,ACCOUNTED_DR
,ACCOUNTED_CR
,RESULT_CODE
,OLD_BUDGET_CCID
,TXN_CCID
,ORG_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
) select
l_fc_rec_tab(i).PACKET_ID
,pa_bc_packets_s.nextval --l_fc_rec_tab(i).BC_PACKET_ID
,l_fc_rec_tab(i).PARENT_BC_PACKET_ID
,l_fc_rec_tab(i).BC_COMMITMENT_ID
,l_fc_rec_tab(i).PROJECT_ID
,l_fc_rec_tab(i).TASK_ID
,l_fc_rec_tab(i).EXPENDITURE_TYPE
,l_fc_rec_tab(i).EXPENDITURE_ITEM_DATE
,l_fc_rec_tab(i).SET_OF_BOOKS_ID
,l_fc_rec_tab(i).JE_CATEGORY_NAME
,l_fc_rec_tab(i).JE_SOURCE_NAME
,l_fc_rec_tab(i).STATUS_CODE
,l_fc_rec_tab(i).DOCUMENT_TYPE
,l_fc_rec_tab(i).FUNDS_PROCESS_MODE
,l_fc_rec_tab(i).EXPENDITURE_ORGANIZATION_ID
,l_fc_rec_tab(i).DOCUMENT_HEADER_ID
,l_fc_rec_tab(i).DOCUMENT_DISTRIBUTION_ID
,l_fc_rec_tab(i).BUDGET_VERSION_ID
,l_fc_rec_tab(i).BURDEN_COST_FLAG
,l_fc_rec_tab(i).BALANCE_POSTED_FLAG
,l_fc_rec_tab(i).ACTUAL_FLAG
,l_fc_rec_tab(i).GL_DATE
,l_fc_rec_tab(i).PERIOD_NAME
,l_fc_rec_tab(i).PERIOD_YEAR
,l_fc_rec_tab(i).PERIOD_NUM
,l_fc_rec_tab(i).ENCUMBRANCE_TYPE_ID
,l_fc_rec_tab(i).PROJ_ENCUMBRANCE_TYPE_ID
,l_fc_rec_tab(i).TOP_TASK_ID
,l_fc_rec_tab(i).PARENT_RESOURCE_ID
,l_fc_rec_tab(i).RESOURCE_LIST_MEMBER_ID
,l_fc_rec_tab(i).ENTERED_DR
,l_fc_rec_tab(i).ENTERED_CR
,l_fc_rec_tab(i).ACCOUNTED_DR
,l_fc_rec_tab(i).ACCOUNTED_CR
,l_fc_rec_tab(i).RESULT_CODE
,l_fc_rec_tab(i).OLD_BUDGET_CCID
,l_fc_rec_tab(i).TXN_CCID
,l_fc_rec_tab(i).ORG_ID
,l_fc_rec_tab(i).LAST_UPDATE_DATE
,l_fc_rec_tab(i).LAST_UPDATED_BY
,l_fc_rec_tab(i).CREATED_BY
,l_fc_rec_tab(i).CREATION_DATE
,l_fc_rec_tab(i).LAST_UPDATE_LOGIN
FROM DUAL
WHERE l_fc_rec_tab(i).status_code <> 'Z' ;
print_msg('No rec inserted ['||sql%rowcount);
select count(*)
into l_tab_count
from pa_bc_packets
where packet_id = p_packet_id;
print_msg('Number of rec inserted ['||l_tab_count);
-- populate burden rows for the above inserted rows
-- calling the Populate_burden_cost API in TRXIMPORT api will not insert records into pa_bc_packets
-- for document type 'CC_C_CO','CC_P_CO','CC_C_PAY','CC_P_PAY','AP' so the api should be called
-- with calling mode manipulated with GL or CBC
If p_ext_budget_type = 'CC' Then
l_ext_budget_type := 'CBC';
update pa_bc_packets
set result_code = decode(substr(nvl(result_code,'P'),1,1),'P','F142'
,'F',result_code
,'F142')
,status_code = 'T'
where packet_id = p_packet_id;
* in partial mode (Y), then calling program should have the logic to update the result and status code
* after the successfull completion of import process.
* Note: Since we don't have the origanal transaction reference, we cannot update the partial of
* the result code and status of the transactions in partial mode during TRXIMPORT process. so
* all the transactions will be marked as failed or passed.
**/
/** As discussed with Barbara, Dinakar, Sridhar, Prithi :- CC Transaction Import Strategy
* 1.If the project is burdened, the burdening setup in legacy system may differ from Projects burdening setup.
* So we always assume that, the GL and CC encumbrance import process will import the Burdened Amount.
* and going forward PA will derive the burden amounts based on PA burden setup
*
* 2.When you import CC transactions without calling normal funds check process,
* we assume that PA Encumbrance are populated in CC and GL budgets. so we will not post any
* liqudiation or burden entries into igc interface or gl_bc_packets
*
* 3.The CC calls Pa_enc_import_fck API, we assume that CC is putting raw amount into pa_bc_packets
* so this API will derive the burden amounts based on setup on the PA burden setup
**/
PROCEDURE Pa_enc_import_fck(
p_calling_module IN varchar2 default 'CCTRXIMPORT'
, p_ext_budget_type IN varchar2 default 'GL'
, p_conc_flag IN varchar2 default 'N'
, p_set_of_book_id IN number
, p_packet_id IN number
, p_mode IN varchar2 default 'R'
, p_partial_flag IN varchar2 default 'N'
, x_return_status OUT NOCOPY varchar2
, x_error_msg OUT NOCOPY varchar2
) IS
l_fc_return_status varchar2(100);
pa_funds_control_pkg.status_code_update_autonomous
( p_calling_module => 'TRXIMPORT'
,p_packet_id => p_packet_id
,p_mode => p_mode
,p_partial => l_partial_flag
,p_packet_status => 'T'
,x_return_status => x_return_status
);
* status of the pa_bc_packets and pa_bdgt_acct_balances will be updated
* The return status of this API will be 'S' - success, 'F' - Failure, 'T' - Fatal error
**/
PROCEDURE Pa_enc_import_fck_tieback(
p_calling_module IN varchar2
,p_ext_budget_type IN varchar2 default 'GL'
,p_packet_id IN number
,p_mode IN varchar2 default 'R'
,p_partial_flag IN varchar2 default 'N'
,p_cbc_return_code IN varchar2
,x_return_status OUT NOCOPY varchar2
) IS
l_calling_module varchar2(100);
-- Note: Since don't have the origanal transaction reference, we cannot update the partial of
-- the result code and status of the transactions in partial mode during TRXIMPORT process. so
-- all the transactions will be marked as failed or passed.
l_partial_flag := 'N';
print_msg('Calling status_code_update');
pa_funds_control_pkg.status_code_update
( p_calling_module => 'TRXIMPORT'
,p_packet_id => p_packet_id
,p_mode => p_mode
,p_partial => l_partial_flag
,p_packet_status => p_cbc_return_code
,x_return_status => x_return_status
);
print_msg(' After status_code_update return status ['||x_return_status||']');
pa_funds_control_pkg.status_code_update_autonomous
( p_calling_module => 'TRXIMPORT'
,p_packet_id => p_packet_id
,p_mode => p_mode
,p_partial => l_partial_flag
,p_packet_status => 'T'
,x_return_status => x_return_status
);
/** Update the result code of the transactions based on the partial flag, calling mode and p_mode
* in autonomous transaction. After updating the result code call the status_code update API
**/
PROCEDURE tie_back_result_code
(p_calling_module in varchar2,
p_packet_id in number,
p_partial_flag in varchar2,
p_mode in varchar2,
p_glcbc_return_code in varchar2,
x_return_status OUT NOCOPY varchar2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
select pkt.rowid
,pkt.bc_packet_id
,pkt.status_code
,pkt.result_code
from pa_bc_packets pkt
where pkt.packet_id = p_packet_id
and substr(nvl(result_code,'P'),1,1) = 'P';
l_tab_rowid.delete;
l_tab_bc_pkt_id.delete;
l_tab_status_code.delete;
l_tab_result_code.delete;
-- update the result code of the packets where it is passed
FORALL i IN l_tab_rowid.FIRST .. l_tab_rowid.LAST
UPDATE pa_bc_packets
SET result_code =
decode(p_calling_module,
'GL',
decode(p_partial_flag,
'Y',decode(p_mode,'C','F150','F156'),
'N',decode(p_mode,'C',decode(p_glcbc_return_code,'F','F150',
'R','F151',
'T','F151')
,'R',decode(p_glcbc_return_code,'F','F155',
'R','F155',
'T','F155')
,'A',decode(p_glcbc_return_code,'F','F155',
'R','F155',
'T','F155')
,'F',decode(p_glcbc_return_code,'F','F155',
'R','F155',
'T','F155'))),
'CC',
decode(p_partial_flag,
'Y',decode(p_mode,'C','F152','F158'),
'N',decode(p_mode,'C',decode(p_glcbc_return_code,'F','F152',
'R','F153',
'T','F153')
,'R',decode(p_glcbc_return_code,'F','F157',
'R','F157',
'T','F157')
,'A',decode(p_glcbc_return_code,'F','F157',
'R','F157',
'T','F157')
,'F',decode(p_glcbc_return_code,'F','F157',
'R','F157',
'T','F157'))),
'TRXIMPORT',
decode(p_partial_flag,
'Y',decode(substr(nvl(result_code,'P'),1,1),'P',result_code,'F167'),
'N','F167' ))
WHERE packet_id = p_packet_id
AND bc_packet_id = l_tab_bc_pkt_id(i)
AND substr(nvl(result_code,'P'),1,1) = 'P'
AND nvl(p_glcbc_return_code,'R') <> 'S';