[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 xte.entity_code = 'AP_INVOICES'
AND xla.event_date BETWEEN p_start_date AND p_end_date
AND xla.event_status_code in ('U','I')
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');
fv_utility.delete_fv_bc_orphan
(
p_ledger_id => p_ledger_id,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_status => l_return_status
);
' PSA_AP_BC_PVT.CREATE_EVENT Failed after calling fv_utility.delete_fv_bc_orphan!');
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;
PROCEDURE delete_unprocessed_events
(
p_tab_fc_dist IN Funds_Dist_Tab_Type,
p_calling_sequence IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2
)
IS
l_event_source_info xla_events_pub_pkg.t_event_source_info;
l_path_name := g_full_path || '.delete_unprocessed_events';
psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure delete_unprocessed_events ' );
SELECT event_status_code
INTO l_event_status_code
FROM xla_events e
WHERE 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
);
UPDATE ap_invoice_distributions_all
SET bc_event_id = NULL
WHERE invoice_distribution_id = p_tab_fc_dist(i).inv_distribution_id;
/* Delete the orphan events per Invoice Id*/
IF (l_curr_invoice_id <> p_tab_fc_dist(i).invoice_id) THEN
psa_utils.debug_other_string(g_state_level,l_path_name, 'Deleting Orphan Events');
FOR event_rec IN (SELECT e.*
FROM xla_events e,
xla_transaction_entities t
WHERE e.entity_id = t.entity_id
AND t.application_id = 200
AND t.entity_code = l_event_source_info.entity_type_code
AND NVL(t.source_id_int_1,-99) = l_event_source_info.source_id_int_1 -- Bug 10227913
AND t.ledger_id = l_event_source_info.ledger_id -- Bug 10227913
AND e.budgetary_control_flag = 'Y'
AND NOT EXISTS (SELECT 1
FROM ap_invoice_distributions_all
WHERE invoice_id = l_event_source_info.source_id_int_1
AND bc_event_id = e.event_id)) LOOP
psa_utils.debug_other_string(g_state_level,l_path_name, 'Found Event Id = '||event_rec.event_id);
xla_events_pub_pkg.delete_event
(
p_event_source_info => l_event_source_info,
p_event_id => event_rec.event_id,
p_valuation_method => l_valuation_method,
p_security_context => l_security_context
);
psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in delete_unprocessed_events Procedure' );
psa_utils.debug_other_string(g_state_level,'','End of Procedure delete_unprocessed_events' );
| PROCEDURE - delete_processed_orphan_events
| Description - Delete the payables processed BC events.
| Budgetary Control Optimizer program calls this.
*===========================================================================*/
PROCEDURE delete_processed_orphan_events
(
p_init_msg_list IN VARCHAR2,
p_ledger_id IN NUMBER,
p_calling_sequence IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2
)
IS
l_accounting_date DATE;
SELECT xe.event_id ,
xe.event_status_code ,
xe.process_status_code ,
xah.ae_header_id AE_HEADER_ID ,
xah.gl_transfer_status_code GL_TRANSFER_STATUS_CODE,
NVL(xe.budgetary_control_flag, 'N') BUDGETARY_CONTROL_FLAG ,
xah.accounting_date ACCOUNTING_DATE ,
xah.ledger_id,
ai.org_id
FROM xla_events xe,
xla_ae_headers xah,
xla_transaction_entities xt,
ap_invoices_all ai
WHERE xe.application_id = 200
AND xah.application_id = 200
AND xt.application_id = 200
AND xt.entity_id = xe.entity_id
AND xt.entity_code = 'AP_INVOICES'
AND ai.invoice_id = xt.source_id_int_1
AND xah.ledger_id = p_ledger_id
AND xe.event_id = xah.event_id
AND xe.event_status_code = 'P'
AND xe.process_status_code = 'P'
AND xe.budgetary_control_flag = 'Y'
AND xe.event_type_code <> 'MANUAL'
AND NOT EXISTS (SELECT 'not exists'
FROM ap_invoice_distributions_all aid
WHERE aid.bc_event_id = xe.event_id)
AND NOT EXISTS (SELECT 'not exists'
FROM ap_prepay_history_all aph
WHERE aph.bc_event_id = xe.event_id)
AND NOT EXISTS (SELECT 'not exists'
FROM ap_prepay_app_dists apd
WHERE apd.bc_event_id = xe.event_id)
AND NOT EXISTS (SELECT 'not exists'
FROM ap_self_assessed_tax_dist_all aps
WHERE aps.bc_event_id = xe.event_id)
ORDER BY xe.event_id;
fnd_file.put_line(fnd_file.log ,'>> PSA_AP_BC_PVT.Delete_Processed_Orphan_Events');
l_path_name := g_full_path|| '.delete_processed_orphan_events';
psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure delete_processed_orphan_events ' );
fnd_file.put_line(fnd_file.log ,'The following BC Processed orphan events have been deleted');
fnd_file.put_line(fnd_file.log ,'Event_Id Event_Status_Code Process_Status_Code GL_Transfer_Status_Code Delete_Status');
xla_datafixes_pub.delete_journal_entries
(
x_api_version,
x_init_msg_list,
x_application_id,
orphan_event_rec.event_id,
x_return_status,
x_msg_count,
x_msg_data
);
SELECT start_date
INTO l_accounting_date
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = p_ledger_id
AND orphan_event_rec.ACCOUNTING_DATE BETWEEN start_date AND end_date
AND closing_status='O';
SELECT max(start_date)
INTO l_accounting_date
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = p_ledger_id
AND closing_status ='O';
DELETE gl_bc_packets
WHERE event_id = orphan_event_rec.event_id;
DELETE FROM xla_events
WHERE event_id = orphan_event_rec.event_id;
DELETE FROM xla_trial_balances
WHERE ae_header_id = orphan_event_rec.ae_header_id;
fnd_file.put_line(fnd_file.log ,'Events deleted successfully: ' || l_success_count);
fnd_file.put_line(fnd_file.log ,'Events could not be deleted: ' || l_fail_count);
psa_utils.debug_other_string(g_state_level,l_path_name, 'END of procedure delete_processed_orphan_events ' );
fnd_file.put_line(fnd_file.log ,'<< PSA_AP_BC_PVT.delete_processed_orphan_events');
psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in delete_processed_orphan_events Procedure' );
psa_utils.debug_other_string(g_state_level,l_path_name,'End of Procedure delete_processed_orphan_events' );
END delete_processed_orphan_events;
SELECT 'Y'
FROM ap_invoice_distributions_all aid, xla_events xe
WHERE aid.invoice_distribution_id=p_prepayapply_dist_id
AND aid.bc_event_id = xe.event_id
AND xe.event_status_code = 'P'
AND xe.application_id = 200;
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;
SELECT d.parent_reversal_id,
d.amount,
d.invoice_line_number,
d.invoice_id,
d.prepay_distribution_id
INTO l_rev_dist_id,
l_dist_amount,
l_line_number,
l_invoice_id,
l_prepay_distribution_id
FROM ap_invoice_distributions_all d
WHERE invoice_distribution_id = p_invoice_distribution_id;
SELECT d.bc_event_id,
d.encumbered_flag
INTO l_bc_event_id,
l_encumbered_flag
FROM ap_invoice_distributions_all d
WHERE invoice_distribution_id = l_rev_dist_id;
SELECT d.bc_event_id,
d.encumbered_flag
INTO l_bc_event_id,
l_encumbered_flag
FROM ap_invoice_distributions_all d
WHERE invoice_distribution_id = l_prepay_distribution_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);
delete_unprocessed_events
(
p_tab_fc_dist => p_tab_fc_dist,
p_calling_sequence => p_calling_sequence,
p_return_status => x_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data
);
UPDATE ap_invoice_distributions_all
SET encumbered_flag = 'R',
bc_event_id = null
WHERE invoice_distribution_id = l_PrepayProcessTab(i).inv_distribution_id;
UPDATE ap_prepay_app_dists apad
SET apad.bc_event_id = NULL
WHERE apad.PREPAY_APP_DISTRIBUTION_ID = p_tab_fc_dist(i).inv_distribution_id
AND apad.bc_event_id = p_tab_fc_dist(i).bc_event_id;
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;
psa_utils.debug_other_string(g_state_level,l_api_name,'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 --Added for bug 7592825
AND dist.line_type_lookup_code NOT IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV', 'QV', 'AV') -- added due to bug 5639595
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;