[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';
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,
CLM_DOC_FLAG /* <> */
)
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,
( CASE
WHEN reference1 = 'REQ'
AND ( EXISTS (SELECT 1
FROM po_requisition_headers_all
WHERE To_char(requisition_header_id) =
dist.reference2
AND par_flag = 'Y') ) THEN (SELECT
modification_number
FROM
po_drafts
WHERE
draft_id = To_number(dist.reference4))
WHEN reference1 = 'PO'
AND EXISTS (SELECT 1
FROM po_headers_all pha,
po_doc_style_headers pdsh
WHERE pdsh.style_id = pha.style_id
AND pdsh.clm_flag = 'Y'
AND segment1 = dist.reference4) THEN
(SELECT clm_document_number
FROM
po_headers_all
WHERE
segment1 = dist.reference4)
ELSE dist.reference4
END ) reference_number,
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,
DIST.CLM_DOC_FLAG /* <> */
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;
UPDATE po_bc_distributions pbd
SET event_type_code = (select get_event_type_code(pbd.distribution_type,p_action) from dual)
WHERE packet_id = x_packet_id
AND main_or_backing_code = 'M'
RETURNING sequence_number
BULK COLLECT
INTO
l_id_tbl;
UPDATE po_bc_distributions pbd
SET event_type_code = (SELECT event_type_code
FROM po_bc_distributions pbd1
WHERE pbd1.packet_id = x_packet_id
AND pbd1.sequence_number=l_id_tbl(i))
WHERE packet_id = x_packet_id
AND main_or_backing_code <> 'M'
and pbd.origin_sequence_num = l_id_tbl(i);
l_id_tbl.delete;
SELECT prd.distribution_id BULK COLLECT INTO l_id_tbl
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.event_type_code IN ('PO_PA_CANCELLED','RELEASE_CANCELLED');
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 = l_id_tbl(i) ;
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 ;
FOR rec_events IN ( SELECT DISTINCT xe.event_id,xe.entity_id,
xe.event_type_code,
xe.event_date,
xe.event_status_code,
xe.process_status_code,
pbd.applied_to_header_id_1,
pbd.applied_to_entity_code
FROM xla_events xe,
po_bc_distributions pbd
WHERE NVL(xe.budgetary_control_flag, 'N') ='Y'
AND xe.EVENT_STATUS_CODE in ('U' ,'I')
AND xe.PROCESS_STATUS_CODE IN ('I','D')
AND xe.event_id =pbd.ae_event_id
AND pbd.packet_id <> x_packet_id
AND pbd.ae_event_id IS NOT NULL
AND main_or_backing_code = 'M'
-- AND NVL(status_code,'I') <> 'P'
--Bug 16010392. If event status is U in xla_events and P in po_bc_dists,
--Such event has to be deleted.
AND (pbd.header_id,pbd.event_type_code) IN (SELECT DISTINCT header_id,event_type_code FROM po_bc_distributions WHERE packet_id = x_packet_id)
) LOOP
l_event_count := l_event_count+1;
INSERT INTO XLA_EVENTS_INT_GT
VALUES l_events_tab(i) ;
XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 201);
DELETE FROM po_bc_distributions
WHERE applied_to_header_id_1 = l_events_tab(i).source_id_int_1
AND packet_id <> x_packet_id
AND ae_event_id = l_events_tab(i).event_id;
l_num_of_rows_deleted := SQL%rowcount;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number of rows deleted for event '
||l_events_tab(i).event_id||' are '||l_num_of_rows_deleted );
FOR rec_po_bc_dist IN (SELECT DISTINCT applied_to_header_id_1,segment1,distribution_type,gl_date,event_type_code,reference_number
FROM po_bc_distributions
WHERE packet_id = x_packet_id
AND main_or_backing_code = 'M')
LOOP
IF g_debug_stmt THEN
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Applied_to_header_id_1 ' || rec_po_bc_dist.applied_to_header_id_1);
UPDATE po_bc_distributions
SET ae_event_id = l_event_id
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'
returning sequence_number
BULK COLLECT INTO l_seq_num_tbl;
UPDATE po_bc_distributions pobd
SET pobd.ae_event_id = l_event_id
WHERE pobd.packet_id = x_packet_id
AND pobd.origin_sequence_num = l_seq_num_tbl(i);
l_Seq_num_tbl.delete;
END INSERT_PACKET_CREATE_EVENT;
PROCEDURE delete_unnecessary_events(
p_packet_id IN NUMBER,
p_action IN VARCHAR2
)
IS
TYPE t_event_id IS TABLE OF po_bc_distributions.ae_event_id%TYPE;
l_delete_event NUMBER;
l_api_name CONSTANT varchar2(30) := 'delete_unnecessary_events';
CURSOR to_delete_checkfunds IS
SELECT DISTINCT pbd.ae_event_id,
pbd.segment1,
pbd.applied_to_header_id_1,
pbd.distribution_type
FROM
po_bc_distributions pbd,
xla_events xe,
xla_transaction_entities xte
WHERE
xe.event_id = pbd.ae_event_id
AND xe.EVENT_STATUS_CODE = 'U'
AND xe.PROCESS_STATUS_CODE in ('D', 'I')
AND pbd.packet_id = p_packet_id
AND xte.application_id = 201
AND xte.entity_id = xe.entity_id
AND xte.source_id_int_1 = pbd.header_id;
CURSOR to_delete_invalids IS
SELECT DISTINCT pbd.ae_event_id,
pbd.segment1,
pbd.applied_to_header_id_1,
pbd.distribution_type
FROM
po_bc_distributions pbd,
xla_events xe,
xla_transaction_entities xte
WHERE
xe.event_id = pbd.ae_event_id
AND xe.EVENT_STATUS_CODE = 'P'
AND xe.PROCESS_STATUS_CODE = 'I'
AND pbd.packet_id = p_packet_id
AND xte.application_id = 201
AND xte.entity_id = xe.entity_id
AND xte.source_id_int_1 = pbd.header_id;
/*Gathering the event source info to delete event */
l_event_source_info.legal_entity_id := xle_utilities_grp.Get_DefaultLegalContext_OU(PO_MOAC_UTILS_PVT.Get_Current_Org_Id );
SELECT set_of_books_id
INTO l_event_source_info.ledger_id
FROM hr_operating_units hou
WHERE hou.organization_id = PO_MOAC_UTILS_PVT.Get_Current_Org_Id ;
/*delete draft and invalid events*/
IF (p_action= g_action_RESERVE) THEN
l_progress := '030';
FOR rec_to_del IN to_delete_checkfunds loop
IF g_debug_stmt THEN
PO_DEBUG.debug_var(l_log_head,l_progress,'iteration for event_id', rec_to_del.ae_event_id);
/*have to delete these events*/
l_event_source_info.entity_type_code := get_entity_type_code(rec_to_del.distribution_type,p_action);
xla_events_pub_pkg.DELETE_EVENT
(
p_event_source_info => l_event_source_info,
p_event_id => rec_to_del.ae_event_id,
p_valuation_method => NULL,
p_security_context => l_security_context
);
DELETE FROM po_bc_distributions WHERE packet_id = p_packet_id;
FOR rec_to_del IN to_delete_invalids loop
/*event_ids are collected now*/
/*have to delete these events*/
l_event_source_info.entity_type_code := get_entity_type_code(rec_to_del.distribution_type,p_action);
xla_events_pub_pkg.DELETE_EVENT
(
p_event_source_info => l_event_source_info,
p_event_id => rec_to_del.ae_event_id,
p_valuation_method => NULL,
p_security_context => l_security_context
);
DELETE FROM po_bc_distributions WHERE packet_id = p_packet_id ;
PO_DEBUG.debug_var(l_log_head,l_progress, 'Exception block of Delete_unnecessary_events', SQLERRM);
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, GLBC.CODE_COMBINATION_ID) IN (
SELECT /*+ unnest */
/* DISTINCT glbc1.packet_id,
glbc1.code_combination_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
); */
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 PO_BC_DISTRIBUTIONS PBD,
GL_BC_PACKETS GLBC
WHERE pbd.packet_id = p_packet_id
AND pbd.ae_EVENT_ID = GLBC.EVENT_ID
AND GLBC.TEMPLATE_ID IS NULL
AND GLBC.SOURCE_DISTRIBUTION_ID_NUM_1 = pbd.distribution_id --Bug 16437550
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 PO_BC_DISTRIBUTIONS PBD,
GL_BC_PACKETS_HISTS GLBCH
WHERE pbd.packet_id = p_packet_id
AND pbd.ae_EVENT_ID = GLBCH.EVENT_ID
AND GLBCH.TEMPLATE_ID IS NULL
AND GLBCH.SOURCE_DISTRIBUTION_ID_NUM_1 = pbd.distribution_id --Bug 16437550
);
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
/*Bug13887688 Adding few more lookup_codes which are also WARNINGS*/
('P20','P21','P22','P23','P25','P26','P27', 'P39','P29','P31', 'P35', 'P36', 'P37', 'P38')
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
), /* Updating these cols also for bug#13930578 */
PRD.last_update_date = sysdate,
PRD.last_updated_by = fnd_global.user_id,
PRD.last_update_login = fnd_global.login_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
);
UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
SET PRD.funds_liquidated
=
(
SELECT
Nvl(PRD.funds_liquidated,0) + DECODE(p_action, g_action_UNRESERVE,
Decode(TEMP.CLM_DOC_FLAG,'Y',TEMP.CHANGE_IN_FUNDED_VALUE),0)
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 -- 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
), /* Updating these cols also for bug#13930578 */
PRD.last_update_date = sysdate,
PRD.last_updated_by = fnd_global.user_id,
PRD.last_update_login = fnd_global.login_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
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,
POD.amount_changed_flag
)
=
(
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)
),
NULL -- <13503748>
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
), /* Updating these cols also for bug#13930578 */
POD.last_update_date = sysdate,
POD.last_updated_by = fnd_global.user_id,
POD.last_update_login = fnd_global.login_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
), /* Updating these cols also for bug#13930578 */
PRD.last_update_date = sysdate,
PRD.last_updated_by = fnd_global.user_id,
PRD.last_update_login = fnd_global.login_id
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);
/* Bug : 13984592 : Modifying the update statement to update the
prevent encumbrance flag of backing req to 'Y' only when backing GBPA is encumbered.
The case 2: where there is no backing GBPA , the backing Requistion prevent encumbrance
flag is not updated to 'Y' or 'N'. This is required so that if the backing req flag is
explictly updated to 'Y' the code will not flip to 'N'.
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
); */
update PO_REQ_DISTRIBUTIONS_ALL PRD
set PRD.prevent_encumbrance_flag = 'Y'
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
AND EXEC_DOC.agreement_dist_id IS NOT NULL
) ;
--Bug 15871378 commented out the update on encumbered_flag = 'N' when encumbered_amount = 0
UPDATE PO_DISTRIBUTIONS_ALL POD
SET /*POD.ENCUMBERED_FLAG = DECODE(GREATEST(NVL(POD.ENCUMBERED_AMOUNT,0), 0),
0,'N',
'Y'),*/
POD.CHANGE_IN_FUNDED_VALUE = 0
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.CLM_DOC_FLAG = 'Y'
AND MAIN_PURCH.SEND_TO_GL_FLAG = 'Y'
);
--Bug 15871378 commented out the update on encumbered_flag = 'N' when encumbered_amount = 0
UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
SET /*PRD.Encumbered_Flag = DECODE(GREATEST(NVL(PRD.Encumbered_Amount,0), 0),
0,'N',
'Y')
,*/
PRD.Unencumbered_Amount =
( SELECT MAX(NVL(PRD.Unencumbered_Amount,0))
FROM PO_ENCUMBRANCE_GT TEMP
WHERE TEMP.distribution_id = PRD.distribution_id
AND TEMP.CLM_DOC_FLAG = 'Y'
AND TEMP.distribution_type = g_dist_type_REQUISITION
GROUP BY TEMP.distribution_id
)
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.CLM_DOC_FLAG = 'Y'
AND BACKING_REQ.gl_status_code = 'A'
AND BACKING_REQ.send_to_gl_flag = 'Y'
);
--Bug 15871378 commented out the update on encumbered_flag = 'N' when encumbered_amount = 0
UPDATE PO_REQ_DISTRIBUTIONS_ALL PRD
SET /* PRD.ENCUMBERED_FLAG = DECODE(GREATEST(NVL(PRD.ENCUMBERED_AMOUNT,0), 0),
0,'N',
'Y'),*/
PRD.CHANGE_IN_FUNDED_VALUE = 0
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 PO/PA/RELEASE
AND MAIN_REQ.ORIGIN_SEQUENCE_NUM IS NULL -- DOC IS MAIN DOC
AND MAIN_REQ.GL_STATUS_CODE = 'A'
AND MAIN_REQ.CLM_DOC_FLAG = 'Y'
AND MAIN_REQ.SEND_TO_GL_FLAG = 'Y'
);
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'
), /* Updating these cols also for bug#13930578 */
PRL.last_update_date = sysdate,
PRL.last_updated_by = fnd_global.user_id,
PRL.last_update_login = fnd_global.login_id
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'
), /* Updating these cols also for bug#13930578 */
POLL.last_update_date = sysdate,
POLL.last_updated_by = fnd_global.user_id,
POLL.last_update_login = fnd_global.login_id
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
AND POAH.employee_id = p_employee_id
)
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 --
, segment1
)
select 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
, pba.ENCODED_MSG
, 'Y' --
, poh.segment1
from psa_xla_accounting_errors pba,po_headers_all poh
where pba.event_id = PO_ENCUMBRANCE_POSTPROCESSING.g_event_id
and pba.source_id_int_1 = poh.po_header_id
UNION ALL
select 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
, pba.ENCODED_MSG
, 'Y' --
, prh.segment1
from psa_xla_accounting_errors pba,po_requisition_headers_all prh
where pba.event_id = PO_ENCUMBRANCE_POSTPROCESSING.g_event_id
and pba.source_id_int_1 = prh.requisition_header_id;
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'
);