[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT * from IGC_CC_INTERFACE
WHERE cc_header_id = p_doc_id
AND document_type = p_doc_type;
SELECT
xte.source_id_int_1 CC_HEADER_ID
,pck.source_distribution_id_num_1 CC_ACCT_LINE_ID
,pck.code_combination_id CODE_COMBINATION_ID
,xah.accounting_date CC_TRANSACTION_DATE
,pck.accounted_dr CC_FUNC_DR_AMT
,pck.accounted_cr CC_FUNC_CR_AMT
,pck.period_name PERIOD_NAME
,'E' ACTUAL_FLAG
,pck.ledger_id SET_OF_BOOKS_ID
,pck.encumbrance_type_id ENCUMBRANCE_TYPE_ID
,pck.result_code CBC_RESULT_CODE
,pck.status_code STATUS_CODE
,pck.funding_budget_version_id BUDGET_VERSION_ID
,pck.currency_code CURRENCY_CODE
,p_document_type DOCUMENT_TYPE
,xal.description TRANSACTION_DESCRIPTION
,p_document_type REFERENCE_1
,xte.source_id_int_1 REFERENCE_2
/*Bug 6650138 set to null. We can use it for Version number.
I am not changing it at present as code changes large and hence testing
*/
,NULL REFERENCE_3
,xte.transaction_number REFERENCE_4
,xah.description REFERENCE_5
,pck.packet_id REFERENCE_6
,pck.event_id EVENT_ID
,xah.last_update_date LAST_UPDATE_DATE
,xah.creation_date CREATION_DATE
FROM xla_ae_headers xah
,xla_ae_lines xal
,xla_transaction_entities xte
,gl_bc_packets pck
WHERE xah.ae_header_id = pck.ae_header_id
AND xal.ae_header_id = pck.ae_header_id
AND xal.ae_line_num = pck.ae_line_num
AND xte.entity_id = xah.entity_id
AND pck.event_id = p_event_id
AND pck.ledger_id = p_ledger_id
Order by pck.event_id,pck.source_distribution_id_num_1;
DELETE FROM IGC_CC_INTERFACE
WHERE cc_header_id = p_document_id
AND document_type = p_document_type;
INSERT INTO igc_cc_interface
(
CC_HEADER_ID
,CC_ACCT_LINE_ID
,CODE_COMBINATION_ID
,BATCH_LINE_NUM
,CC_TRANSACTION_DATE
,CC_FUNC_DR_AMT
,CC_FUNC_CR_AMT
,ACTUAL_FLAG
,PERIOD_NAME
,BUDGET_DEST_FLAG
,SET_OF_BOOKS_ID
,ENCUMBRANCE_TYPE_ID
,CBC_RESULT_CODE
,STATUS_CODE
,BUDGET_VERSION_ID
,CURRENCY_CODE
,DOCUMENT_TYPE
,TRANSACTION_DESCRIPTION
,REFERENCE_1
,REFERENCE_2
,REFERENCE_3
,REFERENCE_4
,REFERENCE_5
,REFERENCE_6
,EVENT_ID
,PROJECT_LINE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
)
VALUES
(
p_t_tbl_gl_pck(i).CC_HEADER_ID
,p_t_tbl_gl_pck(i).CC_ACCT_LINE_ID
,p_t_tbl_gl_pck(i).CODE_COMBINATION_ID
,l_batch_line_num
,p_t_tbl_gl_pck(i).CC_TRANSACTION_DATE
,p_t_tbl_gl_pck(i).CC_FUNC_DR_AMT
,p_t_tbl_gl_pck(i).CC_FUNC_CR_AMT
,p_t_tbl_gl_pck(i).ACTUAL_FLAG
,p_t_tbl_gl_pck(i).PERIOD_NAME
,'S'
,p_t_tbl_gl_pck(i).SET_OF_BOOKS_ID
,p_t_tbl_gl_pck(i).ENCUMBRANCE_TYPE_ID
,p_t_tbl_gl_pck(i).CBC_RESULT_CODE
,p_t_tbl_gl_pck(i).STATUS_CODE
,p_t_tbl_gl_pck(i).BUDGET_VERSION_ID
,p_t_tbl_gl_pck(i).CURRENCY_CODE
,p_t_tbl_gl_pck(i).DOCUMENT_TYPE
,p_t_tbl_gl_pck(i).TRANSACTION_DESCRIPTION
,p_t_tbl_gl_pck(i).REFERENCE_1
,p_t_tbl_gl_pck(i).REFERENCE_2
,p_t_tbl_gl_pck(i).REFERENCE_3
,p_t_tbl_gl_pck(i).REFERENCE_4
,p_t_tbl_gl_pck(i).REFERENCE_5
,p_t_tbl_gl_pck(i).REFERENCE_6
,p_t_tbl_gl_pck(i).EVENT_ID
,'N'
,p_t_tbl_gl_pck(i).LAST_UPDATE_DATE
,l_user_id
,p_t_tbl_gl_pck(i).CREATION_DATE
,l_user_id
) ;
Insert into igc_cc_interface
(
CC_HEADER_ID
,CC_ACCT_LINE_ID
,CODE_COMBINATION_ID
,BATCH_LINE_NUM
,CC_TRANSACTION_DATE
,CC_FUNC_DR_AMT
,CC_FUNC_CR_AMT
,ACTUAL_FLAG
,PERIOD_NAME
,BUDGET_DEST_FLAG
,SET_OF_BOOKS_ID
,ENCUMBRANCE_TYPE_ID
,CBC_RESULT_CODE
,STATUS_CODE
,BUDGET_VERSION_ID
,CURRENCY_CODE
,DOCUMENT_TYPE
,TRANSACTION_DESCRIPTION
,REFERENCE_1
,REFERENCE_2
,REFERENCE_3
,REFERENCE_4
,REFERENCE_5
,REFERENCE_6
,EVENT_ID
,PROJECT_LINE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
)
VALUES
(
p_tbl_igc_cc_int(i).CC_HEADER_ID
,p_tbl_igc_cc_int(i).CC_ACCT_LINE_ID
,p_tbl_igc_cc_int(i).CODE_COMBINATION_ID
,i
,p_tbl_igc_cc_int(i).CC_TRANSACTION_DATE
,p_tbl_igc_cc_int(i).CC_FUNC_DR_AMT
,p_tbl_igc_cc_int(i).CC_FUNC_CR_AMT
,p_tbl_igc_cc_int(i).ACTUAL_FLAG
,p_tbl_igc_cc_int(i).PERIOD_NAME
,p_tbl_igc_cc_int(i).BUDGET_DEST_FLAG
,p_tbl_igc_cc_int(i).SET_OF_BOOKS_ID
,p_tbl_igc_cc_int(i).ENCUMBRANCE_TYPE_ID
,p_tbl_igc_cc_int(i).CBC_RESULT_CODE
,p_tbl_igc_cc_int(i).STATUS_CODE
,p_tbl_igc_cc_int(i).BUDGET_VERSION_ID
,p_tbl_igc_cc_int(i).CURRENCY_CODE
,p_tbl_igc_cc_int(i).DOCUMENT_TYPE
,p_tbl_igc_cc_int(i).TRANSACTION_DESCRIPTION
,p_tbl_igc_cc_int(i).REFERENCE_1
,p_tbl_igc_cc_int(i).REFERENCE_2
,p_tbl_igc_cc_int(i).REFERENCE_3
,p_tbl_igc_cc_int(i).REFERENCE_4
,p_tbl_igc_cc_int(i).REFERENCE_5
,p_tbl_igc_cc_int(i).REFERENCE_6
,p_tbl_igc_cc_int(i).EVENT_ID
,p_tbl_igc_cc_int(i).PROJECT_LINE
,p_tbl_igc_cc_int(i).LAST_UPDATE_DATE
,p_tbl_igc_cc_int(i).LAST_UPDATED_BY
,p_tbl_igc_cc_int(i).CREATION_DATE
,p_tbl_igc_cc_int(i).CREATED_BY
);
Put_Debug_Msg(l_full_path, 'Record Inserted :'||SQL%ROWCOUNT);
Select event_id, application_id, event_type_code, event_status_code, entity_id
from xla_events xla
where xla.event_id in
(
Select psa.event_id
from psa_bc_xla_events_gt psa
)
and application_id = 201;
SELECT Source_id_int_1
FROM xla_transaction_entities
WHERE entity_id = p_entity_id;
Select distinct ledger_id, nvl(cbc_po_enable, 'N') cbc_enable
From gl_bc_packets pck,
igc_cc_bc_enable cbc
where event_id = p_event_id
and actual_flag = 'E'
and pck.ledger_id = cbc.set_of_books_id(+)
and exists
(select 1 from gl_ledgers l
where l.ledger_id = pck.ledger_id
and l.ledger_category_code = 'PRIMARY');
SELECT event_id,result_code from psa_bc_xla_events_gt;
delete from psa_bc_xla_events_gt;
SELECT poh.type_lookup_code po_type
FROM po_headers_all poh
where po_header_id = p_document_id;
SELECT SUM(INSTR(UPPER(xal.description), UPPER(b.description)))
INTO l_count
FROM po_lookup_codes b,xla_ae_lines xal,xla_ae_headers xah
WHERE xah.event_id = l_event_details(i_evt).event_id
AND xah.ae_header_id = xal.ae_header_id
AND b.lookup_code IN ('IGC YEAR END RESERVE',
'IGC YEAR END UNRESERVE')
AND b.lookup_type = 'CONTROL ACTIONS';
SELECT type_lookup_code
INTO l_document_subtype
FROM po_requisition_headers_all
WHERE requisition_header_id = l_document_id ;
SELECT release_type, po_header_id
INTO l_document_subtype, l_main_doc_id
FROM po_releases_all
WHERE po_release_id = l_document_id ;
igc_cbc_po_grp.update_cbc_acct_date
(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_document_id => l_document_id
,p_document_type => l_document_type
,p_document_sub_type => l_document_subtype
,p_cbc_acct_date => l_accounting_date
) ;
/* Delete old records from IGC_CC_INTERFACE table */
purge_igc_cc_int(l_main_type, l_main_doc_id);
Insert into igc_cc_interface
(
CC_HEADER_ID
,CC_ACCT_LINE_ID
,CODE_COMBINATION_ID
,BATCH_LINE_NUM
,CC_TRANSACTION_DATE
,CC_FUNC_DR_AMT
,CC_FUNC_CR_AMT
,ACTUAL_FLAG
,BUDGET_DEST_FLAG
,SET_OF_BOOKS_ID
,CURRENCY_CODE
,DOCUMENT_TYPE
,TRANSACTION_DESCRIPTION
,REFERENCE_1
,REFERENCE_2
,REFERENCE_3
,REFERENCE_4
,REFERENCE_5
,REFERENCE_6
,PROJECT_LINE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
)
Values
(
l_t_gl_bc_packets_sbc(l_ind).CC_HEADER_ID
,l_t_gl_bc_packets_sbc(l_ind).CC_ACCT_LINE_ID
,l_t_gl_bc_packets_sbc(l_ind).CODE_COMBINATION_ID
,g_batch_line_const + l_ind
,l_accounting_date
,l_t_gl_bc_packets_sbc(l_ind).CC_FUNC_DR_AMT
,l_t_gl_bc_packets_sbc(l_ind).CC_FUNC_CR_AMT
,l_t_gl_bc_packets_sbc(l_ind).ACTUAL_FLAG
,'C'
,l_t_gl_bc_packets_sbc(l_ind).SET_OF_BOOKS_ID
,l_t_gl_bc_packets_sbc(l_ind).CURRENCY_CODE
,l_t_gl_bc_packets_sbc(l_ind).DOCUMENT_TYPE
,l_t_gl_bc_packets_sbc(l_ind).TRANSACTION_DESCRIPTION
,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_1
,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_2
,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_3
,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_4
,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_5
,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_6
,'N'
,l_t_gl_bc_packets_sbc(l_ind).LAST_UPDATE_DATE
,l_user_id
,l_t_gl_bc_packets_sbc(l_ind).CREATION_DATE
,l_user_id
);
/* Delete records from IGC_CC_INTERFACE table */
DELETE FROM igc_cc_interface
WHERE cc_header_id = l_main_doc_id
AND document_type = l_main_type;
INSERT INTO psa_bc_xla_events_gt(event_id,result_code )
VALUES
(l_tbl_psa_event(Idx).event_id,l_tbl_psa_event(Idx).result_code );