[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| PROCEDURE - DELETE_EVENTS
| Description - Delete the unprocessed BC events.
| Payables call this while sweeping the trxs to next period
*===========================================================================*/
PROCEDURE Delete_Events
(
p_init_msg_list IN VARCHAR2,
p_ledger_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_calling_sequence IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR c_get_unprocessed_events IS
SELECT xla.event_id,
xla.event_type_code,
xla.event_date,
xla.event_status_code,
xla.process_status_code,
xte.entity_id,
xte.legal_entity_id,
xte.entity_code,
xte.source_id_int_1,
xte.source_id_int_2,
xte.source_id_int_3,
xte.source_id_int_4,
xte.source_id_char_1
FROM xla_events xla,
xla_transaction_entities xte
WHERE NVL(xla.budgetary_control_flag, 'N') ='Y'
AND xla.application_id = 200
AND xla.event_date BETWEEN p_start_date AND p_end_date
AND xla.event_status_code = 'U'
AND xla.process_status_code <> 'P' --Bug#6857834
AND xla.entity_id = xte.entity_id
AND xla.application_id = xte.application_id
AND xte.ledger_id = p_ledger_id;
l_debug_loc VARCHAR2(30) := 'Delete_Events';
fnd_file.put_line(fnd_file.log ,'>> PSA_AP_BC_PVT.Delete_EVENTS');
l_api_name := g_full_path||'.Delete_Events';
psa_utils.debug_other_string(g_state_level,l_api_name, 'BEGIN of procedure Delete_Events..' );
DELETE FROM XLA_EVENTS_INT_GT;
psa_utils.debug_other_string(g_state_level,l_api_name, '# Rows deleted from xla_events_int_gt'|| SQL%ROWCOUNT );
INSERT INTO XLA_EVENTS_INT_GT
VALUES l_events_tab(i) ;
psa_utils.debug_other_string(g_state_level,l_api_name,' # Rows inserted into xla_events_int_gt table:' || l_event_count);
psa_utils.debug_other_string(g_state_level,l_api_name,'Calling XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENT ');
XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 200);
fnd_file.put_line(fnd_file.log ,'The following BC unprocessed/Error events have been deleted');
UPDATE ap_invoice_distributions_all
SET bc_event_id = NULL
WHERE bc_event_id = l_events_tab(i).event_id;
psa_utils.debug_other_string(g_state_level,l_api_name,'# distributions in ap_invoice_distributions_all has been updated to NULL:'||SQL%ROWCOUNT);
UPDATE ap_prepay_history_all aph
SET aph.bc_event_id = NULL
WHERE aph.bc_event_id = l_events_tab(i).event_id;
psa_utils.debug_other_string(g_state_level,l_api_name,'# distributions in ap_prepay_history_all has been updated to NULL:'||SQL%ROWCOUNT);
UPDATE ap_prepay_app_dists apad
SET apad.bc_event_id = NULL
WHERE apad.bc_event_id = l_events_tab(i).event_id;
psa_utils.debug_other_string(g_state_level,l_api_name,'# distributions in ap_prepay_app_dists has been updated to NULL:'||SQL%ROWCOUNT);
fnd_file.put_line(fnd_file.log ,'Count of BC events deleted:' || l_event_count);
fnd_file.put_line(fnd_file.log ,'<< PSA_AP_BC_PVT.Delete_EVENTS');
psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure Delete_Events' );
psa_utils.debug_other_string(g_excep_level,l_api_name,'Error in Delete_Events Procedure' );
psa_utils.debug_other_string(g_state_level,l_api_name,'End of Procedure Delete_Events' );
END Delete_Events;
SELECT 'Y'
FROM ap_invoice_distributions_all aid, xla_events xe
WHERE aid.invoice_distribution_id=p_prepayapply_dist_id
--l_parent_reversal_id
AND aid.bc_event_id = xe.event_id
AND xe.event_status_code = 'P'
AND xe.application_id = 200;
SELECT 'Encumbered line exist' from dual
WHERE EXISTS
(SELECT '1' FROM ap_invoice_distributions_all
WHERE bc_event_id = p_event_id
AND invoice_id = p_invoice_id
AND encumbered_flag = 'Y'
);
SELECT parent_reversal_id, encumbered_flag
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_inv_dist_id;
SELECT charge_applicable_to_dist_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_inv_dist_id;
SELECT LINE_TYPE_LOOKUP_CODE parent_dist_type
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_inv_dist_id;
SELECT NVL(pod.accrue_on_receipt_flag,'N')
FROM ap_invoice_distributions_all D,
po_distributions_all pod
WHERE D.invoice_distribution_id = p_inv_dist_id
AND D.po_distribution_id IS NOT NULL
AND D.po_distribution_id = pod.po_distribution_id;
SELECT D.po_distribution_id
FROM ap_invoice_distributions_all D
WHERE D.invoice_distribution_id = p_inv_dist_id;
DELETE from psa_bc_xla_events_gt;
psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
DELETE from xla_acct_prog_events_gt;
psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_acct_prog_events_gt: ' || SQL%ROWCOUNT);
DELETE from xla_ae_headers_gt;
psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_headers_gt: ' || SQL%ROWCOUNT);
DELETE from xla_ae_lines_gt;
psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_ae_lines_gt: ' || SQL%ROWCOUNT);
DELETE from xla_validation_lines_gt;
psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows deleted of xla_validation_lines_gt: ' || SQL%ROWCOUNT);
psa_utils.debug_other_string(g_state_level,l_api_name,'Budgetary Control API will not delete this event:'||p_tab_fc_dist(i) .bc_event_id);
psa_utils.debug_other_string(g_state_level,l_api_name,'Calling XLA_EVENTS_PUB_PKG.DELETE_EVENT to delete Event Id :'||p_tab_fc_dist(i).bc_event_id);
XLA_EVENTS_PUB_PKG.DELETE_EVENT(
p_event_source_info => l_event_source_info,
p_event_id => p_tab_fc_dist(i).bc_event_id,
p_valuation_method => l_valuation_method,
p_security_context => l_security_context);
psa_utils.debug_other_string(g_state_level,l_api_name,'After Delete of Event: '||p_tab_fc_dist(i).bc_event_id);
UPDATE ap_invoice_distributions_all
SET bc_event_id = NULL
WHERE bc_event_id = p_tab_fc_dist(i).bc_event_id
AND invoice_id = p_tab_fc_dist(i).invoice_id
AND invoice_distribution_id <> p_tab_fc_dist(i).inv_distribution_id;
psa_utils.debug_other_string(g_state_level,l_api_name,'Updated bc_event_id of '||SQL%ROWCOUNT||' distributions to NULL.');
UPDATE ap_prepay_history_all aph
SET aph.bc_event_id = l_event_id
WHERE aph.invoice_id = p_tab_fc_dist(i).invoice_id
and transaction_type = l_event_type_code
AND (aph.bc_event_id IS NULL or
aph.bc_event_id = p_tab_fc_dist(i).bc_event_id)
AND aph.prepay_history_id = (select max(prepay_history_id)
from ap_prepay_app_dists apd
where prepay_app_distribution_id = p_tab_fc_dist(i).inv_distribution_id);
psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows updated of ap_prepay_history_all: ' || SQL%ROWCOUNT);
UPDATE ap_prepay_app_dists apad
SET apad.bc_event_id = l_event_id
WHERE apad.PREPAY_APP_DISTRIBUTION_ID = p_tab_fc_dist(i).inv_distribution_id
AND (apad.bc_event_id IS NULL or
apad.bc_event_id = p_tab_fc_dist(i).bc_event_id);
psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows updated of ap_prepay_app_dists: ' || SQL%ROWCOUNT);
UPDATE ap_invoice_distributions_all aid
SET bc_event_id = l_event_id
WHERE aid.invoice_id = p_tab_fc_dist(i).invoice_id
AND aid.invoice_line_number = p_tab_fc_dist(i).inv_line_num
AND aid.invoice_distribution_id = p_tab_fc_dist(i).inv_distribution_id;
psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows updated of ap_invoice_distributions_all: ' || SQL%ROWCOUNT);
UPDATE ap_self_assessed_tax_dist_all sad
SET bc_event_id = l_event_id
WHERE sad.invoice_id = p_tab_fc_dist(i).invoice_id
AND sad.invoice_line_number = p_tab_fc_dist(i).inv_line_num
AND sad.invoice_distribution_id = p_tab_fc_dist(i).inv_distribution_id;
,'Number o f rows updated of ap_self_assesed_tax_dist_all: '
|| SQL%ROWCOUNT);
INSERT into psa_bc_xla_events_gt(event_id,result_code)
VALUES (l_bc_event_tab(i).event_id,'XLA_UNPROCESSED');
psa_utils.debug_other_string(g_state_level,l_api_name,'Number of rows inserted in psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
SELECT 'Same bc_event_id stamped for prepay as well non-prepay distributions'
INTO l_sameBCevent
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = p_tab_fc_dist(1).invoice_id
AND isprepaydist( aid1.invoice_distribution_id
,aid1.invoice_id
,aid1.line_type_lookup_code)='Y'
AND aid1.bc_event_id IN (SELECT aid2.bc_event_id
FROM ap_invoice_distributions_all aid2
WHERE aid1.invoice_id = aid2.invoice_id
AND isprepaydist( aid2.invoice_distribution_id
,aid2.invoice_id
,aid2.line_type_lookup_code)='N');
SELECT decode(min(p.status_code),'A', 'S', 'F'),
min(p.status_code)
INTO p_tab_fc_dist(i).result_code,
p_tab_fc_dist(i).status_code
FROM psa_bc_xla_events_gt e,
gl_bc_packets p,
xla_distribution_links xdl,
ap_prepay_app_dists apad
WHERE xdl.event_id = e.event_id
AND apad.PREPAY_APP_DISTRIBUTION_ID = p_tab_fc_dist(i).inv_distribution_id
AND xdl.source_distribution_id_num_1 = APAD.Prepay_App_Dist_ID
AND apad.bc_event_id = xdl.event_id
AND p.event_id = xdl.event_id
AND p.source_distribution_id_num_1 = xdl.source_distribution_id_num_1
AND p.source_distribution_type = xdl.source_distribution_type
AND p.ae_header_id = xdl.ae_header_id
AND p.ae_line_num = xdl.ae_line_num
GROUP BY apad.PREPAY_APP_DISTRIBUTION_ID;
SELECT decode(min(p.status_code),'A', 'S', 'F'),
min(p.status_code)
INTO p_tab_fc_dist(i).result_code,
p_tab_fc_dist(i).status_code
FROM psa_bc_xla_events_gt e,
gl_bc_packets p,
xla_distribution_links xdl
WHERE xdl.event_id = e.event_id
AND xdl.source_distribution_id_num_1 = p_tab_fc_dist(i).inv_distribution_id
AND p.event_id = xdl.event_id
AND p.source_distribution_id_num_1 = xdl.source_distribution_id_num_1
AND p.source_distribution_type = xdl.source_distribution_type
AND p.ae_header_id = xdl.ae_header_id
AND p.ae_line_num = xdl.ae_line_num
GROUP BY p.source_distribution_id_num_1;
SELECT l.lookup_code
INTO p_fc_result_code
FROM gl_lookups l
WHERE lookup_type = 'FUNDS_CHECK_RESULT_CODE'
AND EXISTS ( SELECT 'x'
FROM gl_bc_packets bc,
psa_bc_xla_events_gt e
WHERE bc.event_id = e.event_id
AND bc.result_code like 'F%'
AND bc.result_code = l.lookup_code
)
AND rownum = 1;
| Procedure to process the gl_fundschecker failure code. It updates
| all the unapproved invoice distributions associated for a invoice if
| p_dist_line_num is null or a particular invoice distribution line if
| p_dist_line_num is provided with the given packet_id. It then retrieves
| the gl_fundschecker failure result code and determines which message to
| return to let the user know why fundschecking failed.
|
| PARAMETERS
| p_invoice_id: Invoice Id
| p_inv_line_num
| p_dist_line_num
| p_packet_id
| p_return_message_name - Variable to contain the return message name
| of why fundschecking failed to be populated by
| the procedure.
| p_calling_sequence: Debugging string to indicate path of module
| calls to be printed out NOCOPY upon error.
|
| NOTE
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*==========================================================================*/
PROCEDURE Process_Fundschk_Failure_Code(
p_invoice_id IN NUMBER,
p_inv_line_num IN NUMBER,
p_dist_line_num IN NUMBER,
p_return_message_name IN OUT NOCOPY VARCHAR2,
p_calling_sequence IN VARCHAR2) IS
l_api_name VARCHAR(240);
SELECT meaning
INTO p_return_message_name
FROM fnd_lookups
WHERE lookup_type = 'FUNDS_CHECK_RESULT_CODE'
AND lookup_code = l_fc_result_code;
SELECT charge_applicable_to_dist_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_inv_dist_id;
SELECT LINE_TYPE_LOOKUP_CODE parent_dist_type
,AMOUNT parent_dist_amount FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_inv_dist_id;
SELECT decode(p_invoice_type_code,
'CREDIT','CREDIT MEMO',
'DEBIT', 'DEBIT MEMO',
'PREPAYMENT','PREPAYMENT',
'INVOICE')||' '||
decode(p_calling_mode,'CANCEL','CANCELLED','VALIDATED')
INTO l_event_type_code
FROM dual;
SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE invoice_id = p_invoice_id
AND invoice_distribution_id = p_invoice_dist_id
AND org_id =p_org_id
AND po_distribution_id is not null;
SELECT D.dist_code_combination_id,
D.po_distribution_id,
PD.code_combination_id,
nvl(D.quantity_invoiced, 0),
nvl(PD.quantity_ordered,0)- nvl(PD.quantity_cancelled,0),
nvl(PD.amount_ordered,0) - nvl(PD.amount_cancelled,0),
nvl(D.exchange_rate, 1),
nvl(PLL.match_option, 'P'),
PLT.matching_basis,
D.matched_uom_lookup_code,
RSL.item_id,
PLL.unit_meas_lookup_code,
nvl(D.amount, 0),
decode(I.invoice_currency_code,
SP.base_currency_code,nvl(D.amount,0),
nvl(D.base_amount,0)),
nvl(D.base_invoice_price_variance, 0),
nvl(D.base_quantity_variance, 0),
nvl(D.exchange_rate_variance, 0),
NVL(PD.accrue_on_receipt_flag,'N'),
I.invoice_currency_code,
D.accounting_date,
D.period_name,
PER.period_num,
PER.period_year,
PER.quarter_num,
D.line_type_lookup_code,
nvl(D.tax_recoverable_flag, 'N'),
PD.recovery_rate,
PLL.tax_code_id,
nvl(D.base_amount_variance,0),
I.invoice_date,
I.vendor_id,
I.vendor_site_id,
decode(I.invoice_currency_code,SP.base_currency_code,1,nvl(PD.rate,1)),
nvl(PLL.price_override,0)
FROM
ap_invoice_distributions D,
ap_invoices_all I,
ap_invoice_lines L,
po_distributions PD,
po_lines PL,
po_line_types PLT,
po_line_locations PLL,
po_headers PH,
rcv_transactions RTXN,
rcv_shipment_lines RSL,
gl_period_statuses PER,
po_vendors V,
ap_system_parameters SP
WHERE D.invoice_id = I.invoice_id
AND D.invoice_line_number = L.line_number
AND I.invoice_id = p_invoice_id
AND D.invoice_distribution_id = p_invoice_dist_id
AND L.line_number = p_inv_line_num
AND I.org_id =p_org_id
AND L.invoice_id = D.invoice_id
AND nvl(SP.org_id,-999) = nvl(I.org_id,-999)
AND I.vendor_id = V.vendor_id
AND D.po_distribution_id = PD.po_distribution_id
AND PD.line_location_id = PLL.line_location_id
AND PL.po_header_id = PD.po_header_id
AND PLT.line_type_id = PL.line_type_id
AND PD.po_header_id = PH.po_header_id
AND PL.po_line_id = PD.po_line_id
AND D.rcv_transaction_id = RTXN.transaction_id (+)
AND RTXN.shipment_line_id = RSL.shipment_line_id (+)
AND D.posted_flag in ('N', 'P')
AND nvl(D.encumbered_flag, 'N') in ('N', 'H', 'P')
AND ( D.line_type_lookup_code <> 'AWT'
OR D.line_type_lookup_code <> 'REC_TAX')
AND (D.line_type_lookup_code <> 'PREPAY'
AND D.prepay_tax_parent_id IS NULL)
AND D.period_name = PER.period_name
AND PER.set_of_books_id = p_sob
AND PER.application_id = 200
AND NVL(PER.adjustment_period_flag, 'N') = 'N'
AND D.match_status_flag = 'S'
AND (NOT EXISTS (SELECT 'X'
FROM ap_holds H,
ap_hold_codes C
WHERE H.invoice_id = D.invoice_id
AND ( H.line_location_id is null OR
H.line_location_id = PLL.line_location_id )
AND H.hold_lookup_code = C.hold_lookup_code
AND H.release_lookup_code IS NULL
AND ((C.postable_flag = 'N') OR
(C.postable_flag = 'X'))
AND H.hold_lookup_code <> 'CANT FUNDS CHECK'
AND H.hold_lookup_code <> 'INSUFFICIENT FUNDS'));
SELECT parent_reversal_id, encumbered_flag
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_inv_dist_id;
SELECT bc_event_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_inv_dist_id;
po_api_table_t.DELETE;
SELECT NVL(sum((NVL(dist.amount,0) - NVL(dist.amount_variance,0) - NVL(dist.quantity_variance,0))*nvl(pod.rate,1)), 0) PO_REVERSED_ENCUMBERED_AMOUNT
FROM xla_events evt
,ap_invoice_distributions_all dist
,po_distributions_all pod
WHERE evt.event_status_code = 'P'
AND ( ( p_start_gl_date is not null
and p_start_gl_date <= evt.transaction_date ) or
( p_start_gl_date is null ) )
AND ( (p_end_gl_date is not null
and p_end_gl_date >= evt.transaction_date ) or
(p_end_gl_date is null ) )
AND evt.event_id = dist.bc_event_id
AND evt.application_id = 200
AND evt.event_type_code in ('INVOICE VALIDATED','INVOICE ADJUSTED', 'INVOICE CANCELLED',
'CREDIT MEMO VALIDATED','CREDIT MEMO ADJUSTED','CREDIT MEMO CANCELLED',
'DEBIT MEMO VALIDATED','DEBIT MEMO ADJUSTED','DEBIT MEMO CANCELLED')
AND dist.po_distribution_id is not null
AND dist.po_distribution_id = P_PO_Distribution_Id
AND dist.po_distribution_id = pod.po_distribution_id
AND dist.line_type_lookup_code NOT IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV', 'QV', 'AV') -- added due to bug 5639595
-- ADDED TO RESOLVE SDSU ISSE and need to be revisited
/* and dist.invoice_distribution_id not in(select aid.invoice_distribution_id from ap_invoice_distributions_all aid
where aid.line_type_lookup_code='NONREC_TAX'
and charge_applicable_to_dist_id
in(select invoice_distribution_id from ap_invoice_distributions_all
where bc_event_id is null
and historical_flag ='Y'))*/
-- added by ks not to pick PO that has data in 11i
and not exists (
select 'x' FROM AP_ENCUMBRANCE_LINES_all ael
WHERE ael.invoice_distribution_id = dist.invoice_distribution_id
and encumbrance_type_id = 1001 );
SELECT creation_date
FROM po_distributions_all
WHERE po_distribution_id = l_po_dist_id;
SELECT charge_applicable_to_dist_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_inv_dist_id;
SELECT LINE_TYPE_LOOKUP_CODE parent_dist_type
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_inv_dist_id;