[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_packet_create_event
(
p_status_code IN VARCHAR2,
p_user_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_currency_code IN VARCHAR2,
p_action IN VARCHAR2,--bug#5646605
--
x_packet_id OUT NOCOPY NUMBER
);
PROCEDURE update_successful_rows(
p_doc_type IN VARCHAR2
, p_doc_subtype IN VARCHAR2
, p_action IN VARCHAR2
, p_gl_return_code IN VARCHAR2
);
PROCEDURE update_failed_rows(
p_doc_type IN VARCHAR2
, p_action IN VARCHAR2
);
PROCEDURE insert_report_autonomous(
p_reporting_level IN VARCHAR2
, p_message_text IN VARCHAR2
, p_user_id IN NUMBER
, p_sequence_num_tbl IN po_tbl_number
, p_line_num_tbl IN po_tbl_number
, p_shipment_num_tbl IN po_tbl_number
, p_distribution_num_tbl IN po_tbl_number
, p_distribution_id_tbl IN po_tbl_number
, p_result_code_tbl IN po_tbl_varchar5
, p_message_type_tbl IN po_tbl_varchar1
, p_text_line_tbl IN po_tbl_varchar2000
, p_show_in_psa_flag IN po_tbl_varchar1 --
, p_segment1_tbl IN po_tbl_varchar20 --
, p_distribution_type_tbl IN po_tbl_varchar25 --
, x_online_report_id OUT NOCOPY NUMBER
);
PROCEDURE delete_po_bc_distributions
(
p_packet_id IN NUMBER
) ;
PROCEDURE insert_packet
(
p_status_code IN VARCHAR2,
p_user_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_currency_code IN VARCHAR2,
p_action IN VARCHAR2,--bug#5646605
x_packet_id OUT NOCOPY NUMBER
)
IS
l_proc_name CONSTANT VARCHAR2(30) := 'INSERT_PACKET';
INSERT_PACKET_CREATE_EVENT(
p_status_code => p_status_code,
p_user_id => p_user_id,
p_set_of_books_id=> p_set_of_books_id,
p_currency_code => p_currency_code,
p_action => p_action,--bug#5646605
x_packet_id => x_packet_id
);
DELETE FROM psa_bc_xla_events_gt
WHERE event_id IN ( SELECT distinct ae_event_id
FROM po_bc_distributions
WHERE packet_id <> x_packet_id
);
INSERT into psa_bc_xla_events_gt
(
event_id,
result_code -- Bug #4637958
)
SELECT distinct ae_event_id,'XLA_ERROR'
FROM po_bc_distributions
WHERE packet_id = x_packet_id;
END insert_packet;
PROCEDURE insert_packet_create_event
(
p_status_code IN VARCHAR2,
p_user_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_currency_code IN VARCHAR2,
p_action IN VARCHAR2,--bug#5646605
--
x_packet_id OUT NOCOPY NUMBER
)
IS
l_proc_name CONSTANT VARCHAR2(30) := 'INSERT_PACKET_CREATE_EVENT';
SELECT distinct distribution_id,distribution_type
FROM po_bc_distributions
WHERE packet_id = x_packet_id
AND main_or_backing_code = 'M' ;
SELECT distinct applied_to_header_id_1, segment1, gl_date,distribution_id,distribution_type
FROM po_bc_distributions
WHERE packet_id = x_packet_id
AND main_or_backing_code = 'M' ; -- Bug #4637958
SELECT gl_date
FROM po_bc_distributions
WHERE packet_id <> x_packet_id
AND distribution_id = p_distribution_id
AND ae_event_id is not null
AND event_type_code =p_event_type_code --bug#5646605 used a param instead of package variable
AND main_or_backing_code = 'M' ;
l_delete_event NUMBER; --bug5187228
l_num_of_rows_deleted NUMBER;
l_num_of_rows_inserted NUMBER;
SELECT GL_BC_PACKETS_S.nextval
INTO x_packet_id
FROM DUAL;
INSERT INTO PO_BC_DISTRIBUTIONS
( BC_DISTRIBUTION_ID,
PACKET_ID,
STATUS_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LEDGER_ID,
CURRENCY_CODE,
JE_SOURCE_NAME,
JE_CATEGORY_NAME,
ENTERED_AMT,
ACCOUNTED_AMT,
GL_DATE,
CODE_COMBINATION_ID,
DISTRIBUTION_TYPE,
HEADER_ID,
DISTRIBUTION_ID,
SEQUENCE_NUMBER,
SEGMENT1,
REFERENCE_NUMBER,
APPLIED_TO_APPL_ID,
APPLIED_TO_DIST_LINK_TYPE,
PA_PROJECT_ID,
PA_AWARD_ID,
PA_TASK_ID,
PA_EXP_ORG_ID,
PA_EXP_TYPE,
PA_EXP_ITEM_DATE,
--EVENT_TYPE_CODE,
MAIN_OR_BACKING_CODE,
JE_LINE_DESCRIPTION,
PO_RELEASE_ID,
LINE_ID,
LINE_LOCATION_ID,
ENCUMBRANCE_TYPE_ID,
APPLIED_TO_DIST_ID_1,
APPLIED_TO_ENTITY_CODE,
APPLIED_TO_HEADER_ID_1,
REFERENCE1,
REFERENCE2,
REFERENCE3,
REFERENCE4,
REFERENCE5,
REFERENCE6,
REFERENCE7,
REFERENCE8,
REFERENCE9,
REFERENCE10,
REFERENCE11,
REFERENCE12,
REFERENCE13,
REFERENCE14,
REFERENCE15,
ADJUSTMENT_STATUS,
ORIGIN_SEQUENCE_NUM
)
SELECT
PO_BC_DISTRIBUTIONS_S.nextval,
x_packet_id,
p_status_code,
SYSDATE,
p_user_id,
l_login_id,
SYSDATE,
p_user_id,
p_set_of_books_id,
nvl(DIST.CURRENCY_CODE,p_currency_code),
g_je_source_name_Purchasing,
DIST.JE_CATEGORY_NAME,
DIST.ENTERED_AMOUNT,
DIST.ACCOUNTED_AMOUNT,
DIST.gl_period_date, --
DIST.CODE_COMBINATION_ID,
DIST.DISTRIBUTION_TYPE,
DIST.HEADER_ID,
DIST.DISTRIBUTION_ID,
DIST.SEQUENCE_NUM,
DIST.SEGMENT1,
DIST.SEGMENT1,
l_appl_id,
DECODE(DIST.DISTRIBUTION_TYPE,'REQUISITION','PO_REQ_DISTRIBUTIONS_ALL','PO_DISTRIBUTIONS_ALL') DIST_LINK_TYPE,
DIST.PROJECT_ID,
DIST.AWARD_NUM,
DIST.TASK_ID,
DIST.EXPENDITURE_ORGANIZATION_ID,
DIST.EXPENDITURE_TYPE,
DIST.EXPENDITURE_ITEM_DATE,
--l_event_type_code,
DECODE(DIST.ORIGIN_SEQUENCE_NUM, NULL,'M', 'B_'||DIST.REFERENCE1) MAIN_OR_BACKING_CODE,
DIST.JE_LINE_DESCRIPTION,
DIST.PO_RELEASE_ID,
DIST.LINE_ID,
DIST.LINE_LOCATION_ID,
DIST.ENCUMBRANCE_TYPE_ID,
DIST.DISTRIBUTION_ID,
DECODE(DIST.DISTRIBUTION_TYPE,'REQUISITION','REQUISITION','SCHEDULED', 'RELEASE','BLANKET', 'RELEASE','PURCHASE_ORDER') APPLIED_TO_ENTITY_CODE, -- Bug 4760589
DECODE(DIST.DISTRIBUTION_TYPE,'SCHEDULED',DIST.PO_RELEASE_ID,'BLANKET',DIST.PO_RELEASE_ID,DIST.HEADER_ID), ----APPLIED_TO_HEADER_ID_1
DIST.REFERENCE1,
DIST.REFERENCE2,
DIST.REFERENCE3,
DIST.REFERENCE4,
DIST.REFERENCE5,
DIST.REFERENCE6,
DIST.REFERENCE7,
DIST.REFERENCE8,
DIST.REFERENCE9,
DIST.REFERENCE10,
DIST.REFERENCE11,
DIST.REFERENCE12,
DIST.REFERENCE13,
DIST.REFERENCE14,
DIST.REFERENCE15,
DIST.ADJUSTMENT_STATUS,
DIST.ORIGIN_SEQUENCE_NUM
FROM PO_ENCUMBRANCE_GT DIST
WHERE SEND_TO_GL_FLAG = 'Y' ;
l_num_of_rows_inserted :=sql%rowcount;
PO_DEBUG.debug_var(l_log_head,l_progress,'l_num_of_rows_inserted',l_num_of_rows_inserted);
IF l_num_of_rows_inserted = 0 THEN
x_packet_id :=NULL;
SELECT prd.distribution_id
INTO c_distribution_id
FROM po_req_distributions_all prd,
po_bc_distributions pbd1, -- for Backing
po_bc_distributions pbd2 -- For Main
WHERE pbd1.packet_id = x_packet_id
AND prd.distribution_id = pbd1.distribution_id
AND prd.source_req_distribution_id IS NOT NULL
AND pbd1.main_or_backing_code = 'B_REQ'
AND pbd1.origin_sequence_num = pbd2.sequence_number
AND pbd2.distribution_id = rec_po_bc_dist_back_recreated.distribution_id ;
UPDATE po_bc_distributions pbd
SET main_or_backing_code = 'M' ,event_type_code = 'REQ_RESERVED' , origin_sequence_num = NULL ,
entered_amt = entered_amt * -1 ,accounted_amt = accounted_amt * -1
WHERE pbd.packet_id = x_packet_id
AND pbd.main_or_backing_code = 'B_REQ'
AND pbd.distribution_id = c_distribution_id ;
UPDATE PO_BC_DISTRIBUTIONS PBD
SET PBD.line_number = PBD.bc_distribution_id,
PBD.APPLIED_TO_DIST_ID_2=
(
SELECT ORIG.distribution_id
FROM PO_BC_DISTRIBUTIONS ORIG
WHERE ORIG.sequence_number=PBD.origin_sequence_num
AND ORIG.packet_id = x_packet_id
)
WHERE PBD.packet_id = x_packet_id ;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Bulk Insertion Successful');
SELECT set_of_books_id
INTO l_ledger_id
FROM hr_operating_units hou
WHERE hou.organization_id = l_current_org_id ;
SELECT EVENT_TYPE_CODE INTO c_event_type_code
FROM PO_BC_DISTRIBUTIONS
WHERE packet_id = x_packet_id
AND distribution_id = rec_po_bc_dist.distribution_id
and rownum = 1; -- IR ISO ER 7410448
DELETE
FROM PO_BC_DISTRIBUTIONS
WHERE ae_event_id = l_old_event_id
AND packet_id <> x_packet_id
AND applied_to_header_id_1 = rec_po_bc_dist.applied_to_header_id_1;
xla_events_pub_pkg.DELETE_EVENT
(
p_event_source_info => l_event_source_info,
p_event_id => l_old_event_id,
p_valuation_method => l_valuation_method,
p_security_context => l_security_context
);
PO_DEBUG.debug_var(l_log_head,l_progress,'Event deleted : ',l_old_event_id) ;
l_delete_event := xla_events_pub_pkg.DELETE_ENTITY( p_source_info => l_event_source_info,
p_valuation_method => l_valuation_method,
p_security_context => l_security_context);
DELETE
FROM PO_BC_DISTRIBUTIONS
WHERE ae_event_id = l_old_event_id
AND packet_id <> x_packet_id
AND applied_to_header_id_1 = rec_po_bc_dist.applied_to_header_id_1;
l_num_of_rows_deleted := SQL%rowcount;
PO_DEBUG.debug_var(l_log_head,l_progress,'l_num_of_rows_deleted ',l_num_of_rows_deleted );
if l_num_of_rows_deleted >0 then
xla_events_pub_pkg.DELETE_EVENT
(
p_event_source_info => l_event_source_info,
p_event_id => l_old_event_id,
p_valuation_method => l_valuation_method,
p_security_context => l_security_context
);
PO_DEBUG.debug_var(l_log_head,l_progress,'Event deleted : ',l_old_event_id) ;
l_delete_event := xla_events_pub_pkg.DELETE_ENTITY( p_source_info => l_event_source_info,
p_valuation_method => l_valuation_method,
p_security_context => l_security_context);
UPDATE po_bc_distributions
SET ae_event_id = l_event_id,
event_type_code=l_event_type_code --bug#5646605 update the event type code for the main doc rows
WHERE packet_id = x_packet_id
AND applied_to_header_id_1 = rec_po_bc_dist.applied_to_header_id_1
AND gl_date = rec_po_bc_dist.gl_date
AND main_or_backing_code = 'M' -- Bug #4637958
RETURNING sequence_number
BULK COLLECT
INTO l_seq_num_tbl ; -- to return ids of distributions updated
UPDATE po_bc_distributions pobd
SET pobd.ae_event_id = l_event_id,
pobd.event_type_code=l_event_type_code --bug#5646605 update the event type code for the backing rows
WHERE pobd.packet_id = x_packet_id
AND pobd.origin_sequence_num = l_seq_num_tbl(i);
SELECT pobd1.distribution_id
FROM po_bc_distributions pobd1
WHERE pobd1.origin_sequence_num = l_seq_num_tbl(i)
); */
END INSERT_PACKET_CREATE_EVENT;
TYPE update_enc_amt_flag_tbl_type IS TABLE OF PO_ENCUMBRANCE_GT.update_encumbered_amount_flag%TYPE;
l_update_enc_amt_flag_tbl update_enc_amt_flag_tbl_type;
SELECT
DISTINCT
STATUS_CODE,
RESULT_CODE,
AUTOMATIC_ENCUMBRANCE_FLAG,
SOURCE_DISTRIBUTION_ID_NUM_1,
SOURCE_DISTRIBUTION_TYPE,
TRANSACTION_AMOUNT,
REFERENCE15
BULK COLLECT
INTO l_gl_status_tbl,
l_gl_result_code_tbl,
l_update_enc_amt_flag_tbl,
l_dist_id_tbl,
l_dist_type_tbl,
l_encumbered_amount_change,
l_reference15_tbl
FROM (
SELECT GLBC.status_code STATUS_CODE,
GLBC.result_code RESULT_CODE,
nvl(GLBC.automatic_encumbrance_flag, 'Y') AUTOMATIC_ENCUMBRANCE_FLAG,
GLBC.SOURCE_DISTRIBUTION_ID_NUM_1 SOURCE_DISTRIBUTION_ID_NUM_1,
GLBC.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PO_ENCUMBRANCE_POSTPROCESSING.get_sign_for_amount(pbd.event_type_code,
pbd.main_or_backing_code,
pbd.adjustment_status,
pbd.distribution_type) *
PBD.accounted_amt TRANSACTION_AMOUNT,
PBD.reference15 REFERENCE15
FROM XLA_DISTRIBUTION_LINKS XLD,
PO_BC_DISTRIBUTIONS PBD,
GL_BC_PACKETS GLBC
WHERE GLBC.PACKET_ID IN (SELECT DISTINCT glbc1.packet_id
FROM psa_bc_xla_events_gt ps_ev_Gt ,
GL_BC_PACKETS glbc1
WHERE ps_ev_Gt.event_id = glbc1.event_id)
AND XLD.AE_HEADER_ID = GLBC.ae_header_id
AND xld.ae_line_num = GLBC.ae_line_num
AND xld.event_id = GLBC.event_id
AND GLBC.application_id = xld.application_id
AND GLBC.source_distribution_type = xld.source_distribution_type
AND GLBC.SOURCE_DISTRIBUTION_ID_NUM_1 =
xld.SOURCE_DISTRIBUTION_ID_NUM_1
AND pbd.distribution_id = xld.SOURCE_DISTRIBUTION_ID_NUM_1
AND decode(pbd.distribution_type,g_dist_type_REQUISITION,
'PO_REQ_DISTRIBUTIONS_ALL','PO_DISTRIBUTIONS_ALL') = xld.source_distribution_type
AND pbd.ae_event_id = xld.event_id
AND NVL(PBD.applied_to_dist_id_2, pbd.distribution_id) =
XLD.ALLOC_TO_DIST_ID_NUM_1
AND xld.application_id = 201
AND xld.event_id = pbd.ae_event_id
AND glbc.template_id is null
UNION ALL
SELECT GLBCH.status_code STATUS_CODE,
GLBCH.result_code RESULT_CODE,
nvl(GLBCH.automatic_encumbrance_flag, 'Y') AUTOMATIC_ENCUMBRANCE_FLAG,
GLBCH.SOURCE_DISTRIBUTION_ID_NUM_1 SOURCE_DISTRIBUTION_ID_NUM_1,
GLBCH.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE,
PO_ENCUMBRANCE_POSTPROCESSING.get_sign_for_amount(pbd.event_type_code,
pbd.main_or_backing_code,
pbd.adjustment_status,
pbd.distribution_type) *
PBD.accounted_amt TRANSACTION_AMOUNT,
PBD.reference15 REFERENCE15
FROM XLA_DISTRIBUTION_LINKS XLD,
PO_BC_DISTRIBUTIONS PBD,
GL_BC_PACKETS_HISTS GLBCH
WHERE GLBCH.PACKET_ID IN (SELECT DISTINCT glbch1.packet_id
FROM psa_bc_xla_events_gt ps_ev_Gt ,
GL_BC_PACKETS_HISTS glbch1
WHERE ps_ev_Gt.event_id = glbch1.event_id)
AND XLD.AE_HEADER_ID = GLBCH.ae_header_id
AND xld.ae_line_num = GLBCH.ae_line_num
AND xld.event_id = GLBCH.event_id
AND GLBCH.application_id = xld.application_id
AND GLBCH.source_distribution_type =
xld.source_distribution_type
AND GLBCH.SOURCE_DISTRIBUTION_ID_NUM_1 =
xld.SOURCE_DISTRIBUTION_ID_NUM_1
AND pbd.distribution_id = xld.SOURCE_DISTRIBUTION_ID_NUM_1
AND decode(pbd.distribution_type,g_dist_type_REQUISITION,
'PO_REQ_DISTRIBUTIONS_ALL','PO_DISTRIBUTIONS_ALL') = xld.source_distribution_type
AND pbd.ae_event_id = xld.event_id
AND NVL(PBD.applied_to_dist_id_2, pbd.distribution_id) =
XLD.ALLOC_TO_DIST_ID_NUM_1
AND xld.application_id = 201
AND xld.event_id = pbd.ae_event_id
AND glbch.template_id is null
);
UPDATE PO_ENCUMBRANCE_GT TEMP
SET TEMP.gl_status_code = l_gl_status_tbl(i),
TEMP.gl_result_code = l_gl_result_code_tbl(i),
TEMP.update_encumbered_amount_flag = l_update_enc_amt_flag_tbl(i),
TEMP.encumbered_amount_change = l_encumbered_amount_change(i) -- Bug 4878973
WHERE TEMP.reference15=l_reference15_tbl(i);--bug#5201733 joining using reference15 as this is the unique key
PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Status/Result codes updated on '
|| l_debug_count || ' rows');
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.result_text = l_po_prevent_text
, DISTS.result_type = g_result_WARNING
WHERE DISTS.prevent_encumbrance_flag = 'Y'
AND DISTS.result_text IS NULL;
'Number of prevent rows updated: ' || l_debug_count);
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.result_text =
(SELECT GL_TEXT.description
FROM GL_LOOKUPS GL_TEXT
WHERE GL_TEXT.lookup_type = 'FUNDS_CHECK_RESULT_CODE'
AND GL_TEXT.lookup_code(+) = DISTS.gl_result_code
)
WHERE DISTS.gl_result_code IS NOT NULL
AND DISTS.result_text IS NULL;
'Number of result text rows updated: ' || l_debug_count);
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.result_type = g_result_WARNING
WHERE DISTS.gl_result_code IN
('P20','P21','P22','P23','P25','P26','P27', 'P39')
AND DISTS.gl_status_code IN ('A', 'S');
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.result_type = g_result_SUCCESS
WHERE DISTS.result_type IS NULL
AND DISTS.gl_status_code IN ('A', 'S');
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.result_type = g_result_ERROR
WHERE DISTS.result_type IS NULL
AND DISTS.gl_result_code like 'F%'
AND DISTS.gl_status_code IN ('R', 'F');
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.result_type = g_result_NOT_PROCESSED
, DISTS.result_text = l_not_processed_msg
WHERE DISTS.result_type IS NULL
AND DISTS.gl_result_code like 'P%'
AND DISTS.gl_status_code IN ('R', 'F');
'PO update of text/message type successful');
PROCEDURE update_document_encumbrance(
p_doc_type IN VARCHAR2
, p_doc_subtype IN VARCHAR2
, p_action IN VARCHAR2
, p_gl_return_code IN VARCHAR2
) IS
l_api_name CONSTANT varchar2(40) := 'UPDATE_DOCUMENT_ENCUMBRANCE';
update_successful_rows(
p_doc_type => p_doc_type
, p_doc_subtype => p_doc_subtype
, p_action => p_action
, p_gl_return_code => p_gl_return_code
);
update_failed_rows(
p_doc_type => p_doc_type
, p_action => p_action
);
END update_document_encumbrance;
PROCEDURE update_successful_rows(
p_doc_type IN VARCHAR2
, p_doc_subtype IN VARCHAR2
, p_action IN VARCHAR2
, p_gl_return_code IN VARCHAR2
) IS
l_api_name CONSTANT varchar2(40) := 'UPDATE_SUCCESSFUL_ROWS';
'Updated encumbered_amount_change');
UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
SET
(
PRD.encumbered_flag
, PRD.encumbered_amount
)
=
(
SELECT
--encumbered flag:
DECODE( l_flip_enc_flag
, 'N', PRD.encumbered_flag -- don't flip flag
, l_main_doc_enc_flag_success),
--encumbered amt:
nvl(PRD.encumbered_amount, 0) +
SUM (decode(TEMP.update_encumbered_amount_flag,
'Y', TEMP.encumbered_amount_change,
0)
)
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.distribution_id = PRD.distribution_id
AND TEMP.distribution_type = g_dist_type_REQUISITION
GROUP BY TEMP.distribution_id
)
WHERE PRD.distribution_id in
(
SELECT MAIN_REQ.distribution_id
FROM PO_ENCUMBRANCE_GT MAIN_REQ
WHERE MAIN_REQ.distribution_type = g_dist_type_REQUISITION -- doc is Req
AND MAIN_REQ.origin_sequence_num IS NULL -- doc is main doc
AND MAIN_REQ.gl_status_code = 'A'
AND MAIN_REQ.send_to_gl_flag = 'Y' --bug 3568512: use new column
);
'Updated Main Req dists: ' || l_debug_count);
UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
SET (
PRD.encumbered_flag
, PRD.prevent_encumbrance_flag
)
=
( SELECT
--encumbered_flag:
--unreserve old rows, reserve new rows
DECODE( TEMP.adjustment_status
, g_adjustment_status_OLD, 'N'
, g_adjustment_status_NEW, 'Y'
, TEMP.encumbered_flag
),
--prevent_encumbrance_flag:
--old rows are marked prevent-enc for future actions
DECODE( TEMP.adjustment_status
, g_adjustment_status_OLD, 'Y'
, TEMP.prevent_encumbrance_flag
)
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.distribution_id = PRD.distribution_id
AND TEMP.distribution_type = g_dist_type_REQUISITION
)
WHERE PRD.distribution_id IN
(
SELECT MAIN_REQ.distribution_id
FROM PO_ENCUMBRANCE_GT MAIN_REQ
WHERE MAIN_REQ.distribution_type = g_dist_type_REQUISITION
AND MAIN_REQ.origin_sequence_num is NULL
AND MAIN_REQ.gl_status_code = 'A'
);
'Updated Req Split dists: ' || l_debug_count);
UPDATE PO_DISTRIBUTIONS_ALL POD
SET
(
POD.encumbered_flag,
POD.encumbered_amount
)
=
(
SELECT
--encumbered flag:
DECODE( l_flip_enc_flag
, 'N', POD.encumbered_flag -- don't flip flag
, l_main_doc_enc_flag_success),
--encumbered amt:
nvl(POD.encumbered_amount, 0) +
SUM (decode(TEMP.update_encumbered_amount_flag,
'Y',TEMP.encumbered_amount_change,
0)
)
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.distribution_id = POD.po_distribution_id
AND TEMP.distribution_type <> g_dist_type_REQUISITION
GROUP BY TEMP.distribution_id
)
WHERE POD.po_distribution_id in
(
SELECT MAIN_PURCH.distribution_id
FROM PO_ENCUMBRANCE_GT MAIN_PURCH
WHERE MAIN_PURCH.distribution_type <> g_dist_type_REQUISITION
-- doc is PO/PA/Release
AND MAIN_PURCH.origin_sequence_num IS NULL -- doc is main doc
AND MAIN_PURCH.gl_status_code = 'A'
AND MAIN_PURCH.send_to_gl_flag = 'Y' --bug 3568512: use new column
);
'Updated Main PO/Rel dists: ' || l_debug_count);
UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
SET
(
PRD.encumbered_flag,
PRD.encumbered_amount
)
=
(
SELECT
--encumbered flag:
MAX(DECODE( l_flip_enc_flag
, 'N', PRD.encumbered_flag --don't flip flag
, l_backing_req_enc_flag_success)),
--encumbered amt:
nvl(PRD.encumbered_amount, 0) +
SUM(decode(TEMP.update_encumbered_amount_flag
, 'Y',TEMP.encumbered_amount_change
, 0))
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.distribution_id = PRD.distribution_id
AND TEMP.distribution_type = g_dist_type_REQUISITION
GROUP BY TEMP.distribution_id
--: added MAX, SUM and GROUP BY operators
)
WHERE PRD.distribution_id in
(
SELECT BACKING_REQ.distribution_id
FROM PO_ENCUMBRANCE_GT BACKING_REQ
WHERE BACKING_REQ.distribution_type = g_dist_type_REQUISITION
AND BACKING_REQ.origin_sequence_num IS NOT NULL
AND BACKING_REQ.gl_status_code = 'A'
AND BACKING_REQ.send_to_gl_flag = 'Y' --bug 3568512: use new column
);
'Updated Backing Req dists: ' || l_debug_count);
UPDATE PO_DISTRIBUTIONS_ALL POD
SET
(
POD.encumbered_amount,
POD.unencumbered_amount
)
=
(
SELECT
nvl(POD.encumbered_amount, 0) +
SUM(decode(CURRENT_DOC.update_encumbered_amount_flag,
'Y',CURRENT_DOC.encumbered_amount_change,
0)
),
nvl(POD.unencumbered_amount, 0) -
SUM(CURRENT_DOC.encumbered_amount_change)
FROM PO_ENCUMBRANCE_GT CURRENT_DOC
WHERE CURRENT_DOC.distribution_id = POD.po_distribution_id
AND CURRENT_DOC.distribution_type IN
(g_dist_type_AGREEMENT, g_dist_type_PLANNED)
GROUP BY CURRENT_DOC.distribution_id
)
WHERE POD.po_distribution_id in
(
SELECT BACKING_PURCH.distribution_id
FROM PO_ENCUMBRANCE_GT BACKING_PURCH
WHERE BACKING_PURCH.distribution_type IN
(g_dist_type_AGREEMENT, g_dist_type_PLANNED) -- PA/PPO
AND BACKING_PURCH.origin_sequence_num IS NOT NULL -- backing doc
AND (BACKING_PURCH.gl_status_code = 'A'
OR (BACKING_PURCH.gl_status_code IS NULL
AND BACKING_PURCH.prevent_encumbrance_flag = 'N')
--bug 3568512: do not filter on send_to_gl_flag = 'Y' because
--even if backing BPA/GA was not sent to GL, its
--unencumbered_amount needs to be updated. for these rows,
--we do not update encumbered_amount unless the row was sent
--to GL; the setting of update_enc_amt_flag checks this
'Updated Backing PA/PPO dists: ' || l_debug_count);
UPDATE PO_DISTRIBUTIONS_ALL POD
SET
POD.unencumbered_quantity =
(SELECT
GREATEST
( 0,
nvl(POD.unencumbered_quantity, 0)
+
(DECODE( p_action
-- if Reserving an SR, add to unenc qty
, g_action_RESERVE, 1
-- if cancelling credit memo, add to unenc qty
, g_action_CR_MEMO_CANCEL, 1
-- all other actions on SR reduce PPO unenc qty
, -1
)
*
SUM (PPO_DISTS.qty_open))
)
FROM PO_ENCUMBRANCE_GT PPO_DISTS
WHERE PPO_DISTS.distribution_id = POD.po_distribution_id
AND PPO_DISTS.distribution_type = g_dist_type_PLANNED
GROUP BY PPO_DISTS.distribution_id
)
WHERE POD.po_distribution_id IN
(
SELECT MAIN_SR.source_distribution_id -- get backing PPO's id
FROM PO_ENCUMBRANCE_GT MAIN_SR
WHERE MAIN_SR.distribution_type = g_dist_type_SCHEDULED
AND MAIN_SR.origin_sequence_num IS NULL
-- the main doc is a Scheduled Release
AND MAIN_SR.gl_status_code = 'A'
AND MAIN_SR.send_to_gl_flag = 'Y' --bug 3568512: use new column
);
'Updated Backing PPO dists: ' || l_debug_count);
UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
SET PRD.prevent_encumbrance_flag
=
(
SELECT
DECODE(
MAX(
DECODE( EXEC_D.agreement_dist_id -- only present if main doc is encumbered
, NULL, 1 -- no backing PA or backing PA not encumberable
, 2 -- this PO dist has backing encumbered PA
)
)
, 1, 'N' --if max is 1, then no backing Enc PA, so prevent_enc_flag <= N
, 'Y' -- if max is 2, then there is backing Enc PA, so prevent_enc_flag <= Y
)
FROM PO_ENCUMBRANCE_GT EXEC_D
WHERE EXEC_D.req_distribution_id = PRD.distribution_id
)
WHERE PRD.distribution_id IN
(
SELECT EXEC_DOC.req_distribution_id
FROM PO_ENCUMBRANCE_GT EXEC_DOC
WHERE EXEC_DOC.distribution_type
IN (g_dist_type_STANDARD, g_dist_type_BLANKET, g_dist_type_PLANNED)
AND EXEC_DOC.req_distribution_id IS NOT NULL
AND EXEC_DOC.gl_status_code = 'A'
AND EXEC_DOC.send_to_gl_flag = 'Y' --bug 3568512
);
END update_successful_rows;
PROCEDURE update_failed_rows(
p_doc_type IN VARCHAR2
, p_action IN VARCHAR2
) IS
l_api_name CONSTANT varchar2(40) := 'UPDATE_FAILED_ROWS';
UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
SET PRD.failed_funds_lookup_code
=
(
SELECT TEMP.gl_result_code
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.distribution_id = PRD.distribution_id
AND TEMP.distribution_type = g_dist_type_REQUISITION
AND rownum = 1
-- handles case with 2 same dist_ids in same packet (Adjust)
)
WHERE PRD.distribution_id in
(
SELECT REQ_DISTS.distribution_id
FROM PO_ENCUMBRANCE_GT REQ_DISTS
WHERE REQ_DISTS.distribution_type = g_dist_type_REQUISITION
AND REQ_DISTS.origin_sequence_num IS NULL
AND REQ_DISTS.gl_status_code = 'R'
AND REQ_DISTS.prevent_encumbrance_flag = 'N'
);
'Updated Req dists failed funds code: ' || l_debug_count);
UPDATE PO_DISTRIBUTIONS_ALL POD
SET POD.failed_funds_lookup_code
=
(
SELECT TEMP.gl_result_code
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.distribution_id = POD.po_distribution_id
AND TEMP.distribution_type <> g_dist_type_REQUISITION
AND rownum = 1
-- handles case with 2 same dist_ids in same packet (Adjust)
)
WHERE POD.po_distribution_id in
(
SELECT PO_DISTS.distribution_id
FROM PO_ENCUMBRANCE_GT PO_DISTS
WHERE PO_DISTS.distribution_type <> g_dist_type_REQUISITION
AND PO_DISTS.origin_sequence_num IS NULL
AND PO_DISTS.gl_status_code = 'R'
AND PO_DISTS.prevent_encumbrance_flag = 'N'
);
'Updated PO/Rel dists failed funds code: ' || l_debug_count);
END update_failed_rows;
UPDATE PO_REQUISITION_LINES_ALL PRL
SET encumbered_flag
=
(
-- Bug 3537764: Modified SET logic to handle the all distributions prevented case
SELECT NVL(min(prd.encumbered_flag), 'N')
FROM PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRD.requisition_line_id = PRL.requisition_line_id
AND NVL(PRD.prevent_encumbrance_flag, 'N') = 'N'
)
WHERE PRL.requisition_line_id IN
(
SELECT TEMP.line_id
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.gl_status_code = 'A'
AND TEMP.distribution_type = g_dist_type_REQUISITION
AND (
(TEMP.send_to_gl_flag = 'Y') --bug 3568512: use new column
or
((p_action = g_action_REQ_SPLIT) and (TEMP.modified_by_agent_flag = 'Y'))
)
-- Bug 3537764: do not filter on prevent_encumbrance flag for req split action
-- This is so we can set the encumbered flag to 'N' for the old pre-split line
-- That old line has prevent_enc_flag = 'Y', and was being missed before this fix.
-- Also, this means that the rollup query in the SET clause can no longer assume
-- that it doesn't need to worry about the all distributions prevented case.
);
'Updated Req lines: ' || l_debug_count);
UPDATE PO_LINE_LOCATIONS_ALL POLL
SET encumbered_flag
=
(
-- Bug 3537764: Modified SET logic to handle the all distributions prevented case
SELECT NVL(min(pod.encumbered_flag), 'N')
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.line_location_id = POLL.line_location_id
AND NVL(POD.prevent_encumbrance_flag, 'N') = 'N'
)
WHERE POLL.line_location_id IN
(
SELECT TEMP.line_location_id
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.gl_status_code = 'A'
AND TEMP.distribution_type IN (g_dist_type_STANDARD, g_dist_type_PLANNED,
g_dist_type_SCHEDULED, g_dist_type_BLANKET)
AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
-- this makes sure that there is atleast one distribution that can be reserved
);
'Updated PO shipments: ' || l_debug_count);
l_update_doc_type_tbl po_tbl_varchar30;
l_update_doc_id_tbl po_tbl_number;
l_insert_doc_type_tbl po_tbl_varchar30;
l_insert_doc_id_tbl po_tbl_number;
l_insert_doc_subtype_tbl po_tbl_varchar30;
l_insert_action_code_tbl po_tbl_varchar30;
l_insert_rev_num_tbl po_tbl_number;
l_update_count NUMBER;
l_insert_count NUMBER;
INSERT INTO PO_SESSION_GT
( key
, num1
)
VALUES
( l_gt_key
, p_doc_id_tbl(i)
);
UPDATE PO_SESSION_GT SCRATCH
SET
( num2
, char1
, char2
)
=
( SELECT
POH.revision_num
, POH.authorization_status
, POH.type_lookup_code
FROM
PO_HEADERS_ALL POH
WHERE POH.po_header_id = SCRATCH.num1
)
WHERE SCRATCH.key = l_gt_key
;
UPDATE PO_SESSION_GT SCRATCH
SET
( num2
, char1
, char2
)
=
( SELECT
POR.revision_num
, POR.authorization_status
, POR.release_type
FROM
PO_RELEASES_ALL POR
WHERE POR.po_release_id = SCRATCH.num1
)
WHERE SCRATCH.key = l_gt_key
;
UPDATE PO_SESSION_GT SCRATCH
SET
( char1
, char2
)
=
( SELECT
PRH.authorization_status
, PRH.type_lookup_code
FROM
PO_REQUISITION_HEADERS_ALL PRH
WHERE PRH.requisition_header_id = SCRATCH.num1
)
WHERE SCRATCH.key = l_gt_key
;
UPDATE PO_SESSION_GT SCRATCH
SET char3 =
( SELECT 'Y'
FROM PO_ACTION_HISTORY POAH
WHERE POAH.object_type_code = p_doc_type
AND POAH.object_id = SCRATCH.num1
AND POAH.action_code IS NULL
)
WHERE SCRATCH.key = l_gt_key
;
SELECT
SCRATCH.num1
, SCRATCH.num2
, SCRATCH.char1
, SCRATCH.char2
, SCRATCH.char3
BULK COLLECT INTO
l_orig_doc_id_tbl
, l_orig_rev_num_tbl
, l_orig_auth_status_tbl
, l_orig_doc_subtype_tbl
, l_orig_null_flag_tbl
FROM
PO_SESSION_GT SCRATCH
WHERE SCRATCH.key = l_gt_key
;
l_update_doc_id_tbl := po_tbl_number();
l_insert_doc_id_tbl := po_tbl_number();
l_insert_doc_subtype_tbl := po_tbl_varchar30();
l_insert_action_code_tbl := po_tbl_varchar30();
l_insert_rev_num_tbl := po_tbl_number();
l_update_doc_id_tbl.EXTEND;
l_update_doc_id_tbl(l_update_doc_id_tbl.LAST) := l_doc_id;
l_insert_doc_id_tbl.EXTEND;
l_insert_doc_subtype_tbl.EXTEND;
l_insert_action_code_tbl.EXTEND;
l_insert_rev_num_tbl.EXTEND;
l_index := l_insert_doc_id_tbl.LAST;
l_insert_doc_id_tbl(l_index) := l_doc_id;
l_insert_doc_subtype_tbl(l_index) := l_doc_subtype;
l_insert_action_code_tbl(l_index) := NULL;
l_insert_rev_num_tbl(l_index) := l_rev_num;
l_insert_doc_id_tbl.EXTEND;
l_insert_doc_subtype_tbl.EXTEND;
l_insert_action_code_tbl.EXTEND;
l_insert_rev_num_tbl.EXTEND;
l_index := l_insert_doc_id_tbl.LAST;
l_insert_doc_id_tbl(l_index) := l_doc_id;
l_insert_doc_subtype_tbl(l_index) := l_doc_subtype;
l_insert_action_code_tbl(l_index) := l_record_action;
l_insert_rev_num_tbl(l_index) := l_rev_num;
l_update_count := l_update_doc_id_tbl.COUNT;
l_insert_count := l_insert_doc_id_tbl.COUNT;
IF (l_update_count > 0) THEN
l_progress := '320';
l_update_doc_type_tbl := po_tbl_varchar30(p_doc_type);
l_update_doc_type_tbl.EXTEND(l_update_count-1, 1);
PO_ACTION_HISTORY_SV.update_action_history(
p_doc_id_tbl => l_update_doc_id_tbl
, p_doc_type_tbl => l_update_doc_type_tbl
, p_action_code => l_record_action
, p_employee_id => p_employee_id
);
IF (l_insert_count > 0) THEN
l_progress := '410';
l_insert_doc_type_tbl := po_tbl_varchar30(p_doc_type);
l_insert_doc_type_tbl.EXTEND(l_insert_count-1, 1);
PO_ACTION_HISTORY_SV.insert_action_history(
p_doc_id_tbl => l_insert_doc_id_tbl
, p_doc_type_tbl => l_insert_doc_type_tbl
, p_doc_subtype_tbl => l_insert_doc_subtype_tbl
, p_doc_revision_num_tbl => l_insert_rev_num_tbl
, p_action_code_tbl => l_insert_action_code_tbl
, p_employee_id => p_employee_id
);
UPDATE PO_LINE_LOCATIONS_ALL POLL
SET POLL.approved_flag = 'R',
POLL.last_update_date = sysdate,
POLL.last_updated_by = fnd_global.user_id,
POLL.last_update_login = fnd_global.login_id
WHERE POLL.po_release_id is NULL
AND nvl(POLL.approved_flag,'N') = 'Y'
AND EXISTS
(
SELECT 'UNRESERVED DISTRIBUTION EXISTS'
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.gl_status_code = l_affected_gl_status_code
AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
AND TEMP.line_location_id = POLL.line_location_id
AND TEMP.distribution_type IN
(g_dist_type_STANDARD, g_dist_type_PLANNED)
);
'PO Shipment flags updated : ' || l_debug_count);
UPDATE PO_HEADERS_ALL POH
SET POH.authorization_status = 'REQUIRES REAPPROVAL',
POH.approved_flag = 'R',
POH.last_update_date = sysdate,
POH.last_updated_by = fnd_global.user_id,
POH.last_update_login = fnd_global.login_id
WHERE nvl(POH.approved_flag,'N') = 'Y' -- if approved
AND EXISTS
(
SELECT 'UNRESERVED DISTRIBUTION EXISTS'
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.gl_status_code = l_affected_gl_status_code
AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
AND TEMP.header_id = POH.po_header_id
AND TEMP.distribution_type IN
(g_dist_type_STANDARD, g_dist_type_PLANNED)
);
'PO Headers updated : ' || l_debug_count);
UPDATE PO_HEADERS_ALL POH
SET POH.authorization_status = 'REQUIRES REAPPROVAL',
POH.approved_flag = 'R',
POH.last_update_date = sysdate,
POH.last_updated_by = fnd_global.user_id,
POH.last_update_login = fnd_global.login_id
WHERE nvl(POH.approved_flag,'N') = 'Y' -- if approved
AND EXISTS
(
SELECT 'UNRESERVED SINGLE DISTRIBUTION EXISTS'
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.gl_status_code = l_affected_gl_status_code
AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
AND TEMP.header_id = POH.po_header_id
AND TEMP.distribution_type = g_dist_type_AGREEMENT
);
'PA Headers updated : ' || l_debug_count);
UPDATE PO_LINE_LOCATIONS_ALL POLL
SET POLL.approved_flag = 'R',
POLL.last_update_date = sysdate,
POLL.last_updated_by = fnd_global.user_id,
POLL.last_update_login = fnd_global.login_id
WHERE POLL.po_release_id is NOT NULL
AND nvl(POLL.approved_flag,'N') = 'Y' -- if approved
AND EXISTS
(
SELECT 'UNRESERVED DISTRIBUTION EXISTS'
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.gl_status_code = l_affected_gl_status_code
AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
AND TEMP.line_location_id = POLL.line_location_id
AND TEMP.distribution_type IN
(g_dist_type_SCHEDULED, g_dist_type_BLANKET)
);
'Release shipments updated : ' || l_debug_count);
UPDATE PO_RELEASES_ALL POR
SET POR.authorization_status = 'REQUIRES REAPPROVAL',
POR.approved_flag = 'R',
POR.last_update_date = sysdate,
POR.last_updated_by = fnd_global.user_id,
POR.last_update_login = fnd_global.login_id
WHERE nvl(POR.approved_flag,'N') = 'Y' -- if approved
AND EXISTS
(
SELECT 'UNRESERVED DISTRIBUTION EXISTS'
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.gl_status_code = l_affected_gl_status_code
AND TEMP.send_to_gl_flag = 'Y' --bug 3568512: use new column
AND TEMP.po_release_id = POR.po_release_id
AND TEMP.distribution_type IN
(g_dist_type_SCHEDULED, g_dist_type_BLANKET)
);
'Release Headers updated : ' || l_debug_count);
UPDATE PO_ENCUMBRANCE_GT DISTS
SET DISTS.result_text =
(
DECODE( DISTS.distribution_type
, g_dist_type_AGREEMENT, ''
, g_dist_type_REQUISITION,
( l_line_token || DISTS.line_num
|| l_delim || l_distribution_token || DISTS.distribution_num
|| l_delim
)
, -- all other docs
( l_line_token || DISTS.line_num
|| l_delim || l_shipment_token || DISTS.shipment_num
|| l_delim || l_distribution_token || DISTS.distribution_num
|| l_delim
)
)
|| DISTS.result_text
)
WHERE DISTS.origin_sequence_num IS NULL
AND DISTS.result_text IS NOT NULL;
SELECT
nvl(DISTS.row_index, DISTS.sequence_num)
, DISTS.line_num
, DISTS.shipment_num
, DISTS.distribution_num
, DISTS.distribution_id
, DISTS.gl_result_code
, DISTS.result_type
, DISTS.result_text
, CASE --
WHEN nvl(DISTS.prevent_encumbrance_flag,'N')='Y' THEN
'Y'
WHEN DISTS.period_name IS NULL THEN
'Y'
ELSE
'N'
END CASE
, DISTS.segment1 --
, DISTS.distribution_type --
BULK COLLECT INTO
l_sequence_num_tbl
, l_line_num_tbl
, l_shipment_num_tbl
, l_distribution_num_tbl
, l_distribution_id_tbl
, l_result_code_tbl
, l_message_type_tbl
, l_text_line_tbl
, l_show_in_psa_tbl --
, l_segment1_tbl --
, l_distribution_type_tbl --
FROM PO_ENCUMBRANCE_GT DISTS
WHERE DISTS.origin_sequence_num IS NULL --main doc only
AND DISTS.result_text IS NOT NULL
AND (DISTS.adjustment_status IS NULL OR
DISTS.adjustment_status = g_adjustment_status_NEW
--bug 3378198: for Adjust, only report on new distributions
)
;
insert_report_autonomous(
p_reporting_level => g_REPORT_LEVEL_DISTRIBUTION
, p_message_text => NULL
, p_user_id => p_user_id
, p_sequence_num_tbl => l_sequence_num_tbl
, p_line_num_tbl => l_line_num_tbl
, p_shipment_num_tbl => l_shipment_num_tbl
, p_distribution_num_tbl => l_distribution_num_tbl
, p_distribution_id_tbl => l_distribution_id_tbl
, p_result_code_tbl => l_result_code_tbl
, p_message_type_tbl => l_message_type_tbl
, p_text_line_tbl => l_text_line_tbl
, p_show_in_psa_flag => l_show_in_psa_tbl --
, p_segment1_tbl => l_segment1_tbl --
, p_distribution_type_tbl=>l_distribution_type_tbl --
, x_online_report_id => x_online_report_id
);
SELECT 'Y'
INTO l_error_rows_flag
FROM PO_ENCUMBRANCE_GT DISTS
WHERE DISTS.result_type = g_result_ERROR
AND rownum = 1;
SELECT 'Y'
INTO l_warning_rows_flag
FROM PO_ENCUMBRANCE_GT DISTS
WHERE DISTS.result_type = g_result_WARNING
AND DISTS.origin_sequence_num IS NULL --bug 3589694
AND rownum = 1;
insert_report_autonomous(
p_reporting_level => g_REPORT_LEVEL_TRANSACTION
, p_message_text => p_message_text
, p_user_id => p_user_id
, p_sequence_num_tbl => NULL
, p_line_num_tbl => NULL
, p_shipment_num_tbl => NULL
, p_distribution_num_tbl => NULL
, p_distribution_id_tbl => NULL
, p_result_code_tbl => NULL
, p_message_type_tbl => NULL
, p_text_line_tbl => NULL
, p_show_in_psa_flag => NULL --
, p_segment1_tbl => NULL --
, p_distribution_type_tbl=> NULL --
, x_online_report_id => x_online_report_id
);
PROCEDURE insert_report_autonomous(
p_reporting_level IN VARCHAR2
, p_message_text IN VARCHAR2
, p_user_id IN NUMBER
, p_sequence_num_tbl IN po_tbl_number
, p_line_num_tbl IN po_tbl_number
, p_shipment_num_tbl IN po_tbl_number
, p_distribution_num_tbl IN po_tbl_number
, p_distribution_id_tbl IN po_tbl_number
, p_result_code_tbl IN po_tbl_varchar5
, p_message_type_tbl IN po_tbl_varchar1
, p_text_line_tbl IN po_tbl_varchar2000
, p_show_in_psa_flag IN po_tbl_varchar1 --
, p_segment1_tbl IN po_tbl_varchar20 --
, p_distribution_type_tbl IN po_tbl_varchar25--
, x_online_report_id OUT NOCOPY NUMBER
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_api_name CONSTANT varchar2(30) := 'INSERT_REPORT_AUTONOMOUS';
SELECT PO_ONLINE_REPORT_TEXT_S.nextval
INTO l_report_id
FROM dual;
INSERT INTO PO_ONLINE_REPORT_TEXT(
online_report_id
, sequence
, last_updated_by
, last_update_date
, created_by
, creation_date
, transaction_type
, message_type
, text_line
, show_in_psa_flag --
)
VALUES(
l_report_id
, 0 -- sequence
, l_user_id -- updated by
, SYSDATE -- update date
, l_user_id -- created by
, SYSDATE -- creation date
, g_module_ENCUMBRANCE -- transaction type
, g_result_TRANSACTION -- message type
, l_message_text
, 'Y' --
);
INSERT INTO PO_ONLINE_REPORT_TEXT(
online_report_id
, sequence
, last_updated_by
, last_update_date
, created_by
, creation_date
, line_num
, shipment_num
, distribution_num
, transaction_id
, transaction_type
, transaction_location
, message_type
, text_line
, show_in_psa_flag --
, segment1 --
, distribution_type --
)
VALUES(
l_report_id
, NVL(p_sequence_num_tbl(i),0)
, l_user_id
, SYSDATE
, l_user_id
, SYSDATE
, p_line_num_tbl(i)
, p_shipment_num_tbl(i)
, p_distribution_num_tbl(i)
, p_distribution_id_tbl(i)
, g_module_ENCUMBRANCE
, p_result_code_tbl(i)
, p_message_type_tbl(i)
, NVL(p_text_line_tbl(i),l_message_text)
, p_show_in_psa_flag(i) --
, p_segment1_tbl(i) --
, p_distribution_type_tbl(i) --
);
END insert_report_autonomous;
PROCEDURE delete_packet_autonomous(
p_packet_id IN NUMBER
)
IS
l_proc_name CONSTANT VARCHAR2(30) := 'DELETE_PACKET_AUTONOMOUS';
SELECT event_id
FROM PSA_BC_XLA_EVENTS_GT;
xla_events_pub_pkg.DELETE_EVENT
(
p_event_source_info => NULL,
p_event_id => REC_EVENTS.event_id,
p_valuation_method => NULL,
p_security_context => NULL
);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Delete Packet GL API Called');
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Delete Packet Committed');
END delete_packet_autonomous;
PROCEDURE Delete_PO_BC_Distributions (
p_packet_id IN NUMBER
)
IS
BEGIN
-- Delete all records for the packet processed from po_bc_distributions
DELETE FROM po_bc_distributions
WHERE packet_id = p_packet_id;
END Delete_PO_BC_Distributions;
UPDATE
PO_BC_DISTRIBUTIONS
SET online_report_id=p_online_report_id
WHERE reference15 in
(
SELECT reference15
FROM po_encumbrance_gt
WHERE send_to_gl_flag='Y'
);