[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(purch_encumbrance_flag,'N')
INTO l_enc_enabled
FROM FINANCIALS_SYSTEM_PARAMS_ALL
WHERE org_id = p_org_id; -- Bug 5487757
| for an invoice and deletes the events from xla.
| Also we null out the bc event values in all tables corresponding to the
| invoice
| PARAMETERS
| p_invoice_id - Invoice_id
| p_line_number- Invoice line number which we are discarding
| p_calling_mode - to check if it called during CANCELING or DISCARDING
| p_calling_sequence -Debugging string to indicate path of module
|
| NOTE
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*==========================================================================*/
PROCEDURE Encum_Unprocessed_Events_Del(
p_invoice_id IN NUMBER,
p_calling_sequence IN VARCHAR2 DEFAULT NULL)
IS
l_curr_calling_sequence VARCHAR2(2000);
SELECT xla.event_id
FROM xla_events xla,
xla_transaction_entities xte,
ap_invoices_all ai
WHERE NVL(xla.budgetary_control_flag, 'N') ='Y'
AND xla.application_id = 200
AND xte.application_id = 200
AND xla.event_status_code <> 'P'
AND xla.process_status_code <> 'P'
AND xla.entity_id = xte.entity_id
AND xla.application_id = xte.application_id
AND NVL(xte.source_id_int_1, -99) = ai.invoice_id
AND xte.entity_code = 'AP_INVOICES'
AND xte.ledger_id = ai.set_of_books_id
AND ai.invoice_id = p_invoice_id;
SELECT org_id
INTO l_org_id
FROM ap_invoices_all
where invoice_id=p_invoice_id;
SELECT nvl(purch_encumbrance_flag,'N')
INTO l_enc_enabled
FROM financials_system_params_all
WHERE org_id = l_org_id;
AP_ACCOUNTING_EVENTS_PKG.delete_invoice_event
(p_accounting_event_id => rec_event.event_id,
p_Invoice_Id => p_invoice_id,
p_calling_sequence => l_curr_calling_sequence);
UPDATE ap_prepay_app_dists
SET bc_event_id = NULL
WHERE prepay_history_id in
(SELECT prepay_history_id
FROM ap_prepay_history_all
WHERE invoice_id = p_invoice_id)
AND bc_event_id =rec_event.event_id;
UPDATE ap_prepay_history_all
SET bc_event_id = NULL
WHERE invoice_id = p_invoice_id
AND bc_event_id = rec_event.event_id;
UPDATE ap_invoice_distributions
SET bc_event_id=NULL
WHERE invoice_id = p_invoice_id
AND bc_event_id=rec_event.event_id
AND nvl(encumbered_flag,'N') <> 'Y';
UPDATE ap_self_assessed_tax_dist_all
SET bc_event_id=NULL
WHERE invoice_id = p_invoice_id
AND bc_event_id=rec_event.event_id
AND nvl(encumbered_flag,'N') <> 'Y';
SELECT sp.set_of_books_id,
nvl(sp.rate_var_gain_ccid, -1),
nvl(sp.rate_var_loss_ccid, -1),
nvl(sp.base_currency_code, 'USD'),
nvl(fp.inv_encumbrance_type_id, -1)
INTO p_set_of_books_id,
p_xrate_gain_ccid,
p_xrate_loss_ccid,
p_base_currency_code,
p_inv_enc_type_id
FROM ap_system_parameters sp,
financials_system_parameters fp,
gl_sets_of_books gls,
ap_invoices ai
WHERE sp.set_of_books_id = gls.set_of_books_id
AND sp.set_of_books_id = ai.set_of_books_id
AND ai.invoice_id = p_invoice_id;
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,
xla_events_gt e
WHERE bc.event_id = e.event_id
AND result_code like 'F%'
AND bc.result_code = l.lookup_code)
AND rownum = 1;
SELECT I.invoice_id, -- invoice_id
I.invoice_num, -- invoice_num
I.legal_entity_id, -- BCPSA bug
I.invoice_type_lookup_code, -- invoice_type_code
D.invoice_line_number, -- inv_line_num
D.invoice_distribution_id , -- inv_distribution_id
D.accounting_date, -- accounting_date
D.LINE_TYPE_LOOKUP_CODE, -- distribution_type
D.amount, -- distribution_amount
D.set_of_books_id, -- set_of_books_id
D.bc_event_id, -- bc_event_id
D.org_id, -- org_id
NULL, --result_code
NULL, --status_code
'N' self_assessed_flag --self_assessed_flag --bug7109594
FROM gl_period_statuses PER,
ap_invoices I,
ap_invoice_distributions_all D,
ap_invoice_lines L
WHERE D.invoice_id = I.invoice_id
AND D.invoice_line_number = L.line_number
AND L.invoice_id = D.invoice_id
AND D.posted_flag in ('N', 'P')
AND nvl(D.encumbered_flag, 'N') in ('N', 'H', 'P')
AND L.line_type_lookup_code NOT IN ('AWT')
AND D.period_name = PER.period_name
AND PER.set_of_books_id = p_set_of_books_id
AND PER.application_id = 200
AND NVL(PER.adjustment_period_flag, 'N') = 'N'
AND I.invoice_id = p_invoice_id
AND D.po_distribution_id is NULL
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
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'
AND H.hold_lookup_code <> 'Encumbrance Acctg Fail'))) --Bug 9136390
UNION ALL
SELECT I.invoice_id, -- invoice_id
I.invoice_num, -- invoice_num
I.legal_entity_id, -- BCPSA bug
I.invoice_type_lookup_code, -- invoice_type_code
D.invoice_line_number, -- inv_line_num
D.invoice_distribution_id , -- inv_distribution_id
D.accounting_date, -- accounting_date
D.LINE_TYPE_LOOKUP_CODE, -- distribution_type
D.amount, -- distribution_amount
D.set_of_books_id, -- set_of_books_id
D.bc_event_id, -- bc_event_id
D.org_id, -- org_id
NULL, --result_code
NULL, --status_code
'N' self_assessed_flag --self_assessed_flag --bug7109594
FROM gl_period_statuses PER,
ap_invoices I,
ap_invoice_distributions_all D,
ap_invoice_lines L,
po_distributions_all pod
WHERE D.invoice_id = I.invoice_id
AND D.invoice_line_number = L.line_number
AND L.invoice_id = D.invoice_id
AND ( (D.line_type_lookup_code = 'ITEM' AND
NVL(pod.accrue_on_receipt_flag,'N') <> 'Y')
OR
(D.line_type_lookup_code NOT IN
( 'RETAINAGE', 'ACCRUAL', 'ITEM' )) )
AND D.posted_flag in ('N', 'P')
AND nvl(D.encumbered_flag, 'N') in ('N', 'H', 'P')
AND L.line_type_lookup_code NOT IN ('AWT')
AND D.period_name = PER.period_name
AND PER.set_of_books_id = p_set_of_books_id
AND PER.application_id = 200
AND NVL(PER.adjustment_period_flag, 'N') = 'N'
AND I.invoice_id = p_invoice_id
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
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'
AND H.hold_lookup_code <> 'Encumbrance Acctg Fail'))) --Bug 9136390
AND D.po_distribution_id IS NOT NULL
AND D.po_distribution_id = pod.po_distribution_id
AND NOT EXISTS ( select 'Advance Exists'
from po_distributions_all pod,
po_headers_all poh,
ap_invoice_distributions_all ainvd,
ap_invoices_all ainv,
po_doc_style_headers pdsa
where pod.po_distribution_id = D.po_distribution_id
and poh.po_header_id = pod.po_header_id
and poh.style_id = pdsa.style_id
and ainv.invoice_id = D.invoice_id
and ainv.invoice_id = ainvd.invoice_id
and ainvd.po_distribution_id = pod.po_distribution_id
and nvl(pdsa.advances_flag, 'N') = 'Y'
and (ainvd.line_type_lookup_code = 'PREPAY'
OR
ainv.invoice_type_lookup_code = 'PREPAYMENT') )
UNION ALL
SELECT I.invoice_id, -- invoice_id
I.invoice_num, -- invoice_num
I.legal_entity_id, -- BCPSA bug
I.invoice_type_lookup_code, -- invoice_type_code
T.invoice_line_number, -- inv_line_num
T.invoice_distribution_id , -- inv_distribution_id
T.accounting_date, -- accounting_date
T.LINE_TYPE_LOOKUP_CODE, -- distribution_type
T.amount, -- distribution_amount
T.set_of_books_id, -- set_of_books_id
T.bc_event_id, -- bc_event_id
T.org_id, -- org_id
NULL, --result_code
NULL, --status_code
T.self_assessed_flag --self_assessed_flag --bug7109594
FROM gl_period_statuses PER,
ap_invoices I,
ap_self_assessed_tax_dist_all T
WHERE T.invoice_id = I.invoice_id
AND T.posted_flag in ('N', 'P')
AND nvl(T.encumbered_flag, 'N') in ('N', 'H', 'P')
AND T.period_name = PER.period_name
AND PER.set_of_books_id = p_set_of_books_id
AND PER.application_id = 200
AND NVL(PER.adjustment_period_flag, 'N') = 'N'
AND I.invoice_id = p_invoice_id
AND T.po_distribution_id is NULL
AND (( T.match_status_flag = 'S')
AND (NOT EXISTS (SELECT 'X'
FROM ap_holds H,
ap_hold_codes C
WHERE H.invoice_id = T.invoice_id
AND H.line_location_id is null
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'
AND H.hold_lookup_code <> 'Encumbrance Acctg Fail'))) --Bug 9136390
UNION ALL
SELECT I.invoice_id, -- invoice_id
I.invoice_num, -- invoice_num
I.legal_entity_id, -- BCPSA bug
I.invoice_type_lookup_code, -- invoice_type_code
T.invoice_line_number, -- inv_line_num
T.invoice_distribution_id , -- inv_distribution_id
T.accounting_date, -- accounting_date
T.LINE_TYPE_LOOKUP_CODE, -- distribution_type
T.amount, -- distribution_amount
T.set_of_books_id, -- set_of_books_id
T.bc_event_id, -- bc_event_id
T.org_id, -- org_id
NULL, --result_code
NULL, --status_code
T.self_assessed_flag --self_assessed_flag --bug7109594
FROM gl_period_statuses PER,
ap_invoices I,
ap_self_assessed_tax_dist_all T
WHERE T.invoice_id = I.invoice_id
AND T.posted_flag in ('N', 'P')
AND nvl(T.encumbered_flag, 'N') in ('N', 'H', 'P')
AND T.period_name = PER.period_name
AND PER.set_of_books_id = p_set_of_books_id
AND PER.application_id = 200
AND NVL(PER.adjustment_period_flag, 'N') = 'N'
AND I.invoice_id = p_invoice_id
AND (( T.match_status_flag = 'S')
AND (NOT EXISTS (SELECT 'X'
FROM ap_holds H,
ap_hold_codes C
WHERE H.invoice_id = T.invoice_id
AND H.line_location_id is null
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'
AND H.hold_lookup_code <> 'Encumbrance Acctg Fail'))) --Bug 9136390
AND T.po_distribution_id is NOT NULL
AND NOT EXISTS ( select 'Advance Exists'
from po_distributions_all pod,
po_headers_all poh,
ap_invoice_distributions_all ainvd,
ap_invoices_all ainv,
po_doc_style_headers pdsa
where pod.po_distribution_id = T.po_distribution_id
and poh.po_header_id = pod.po_header_id
and poh.style_id = pdsa.style_id
and ainv.invoice_id = T.invoice_id
and ainv.invoice_id = ainvd.invoice_id
and ainvd.po_distribution_id = pod.po_distribution_id
and nvl(pdsa.advances_flag, 'N') = 'Y'
and (ainvd.line_type_lookup_code = 'PREPAY'
OR
ainv.invoice_type_lookup_code = 'PREPAYMENT') );
l_log_msg := 'Selecting Org_Id for determining Encumbrance Enabled or not' ;
SELECT org_id
INTO l_org_id
FROM AP_INVOICES_ALL
WHERE invoice_id = p_invoice_id;
| Step 2.5 - Update the encumbered_flag for recoverable tax |
| distributions to R so that these are not sent to PSA |
| for encumbering |
+-----------------------------------------------------------------*/
IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
l_log_msg := ' Update encumbered flag of recoverable ' ||
'tax distributions to R';
Update ap_invoice_distributions_all
set encumbered_flag = 'R'
where invoice_id = p_invoice_id
and line_type_lookup_code = 'REC_TAX';
| Step 3 - Get all the selected distributions for processing |
+-----------------------------------------------------------------*/
IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
l_log_msg := 'Step 3 - Open FundsCntrl_Inv_Dist_Cur Cursor';
SELECT aid.bc_event_id
INTO l_bc_event_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id;
SELECT xe.event_status_code
INTO l_bc_event_status
FROM xla_events xe
WHERE xe.application_id = 200
AND xe.event_id = l_bc_event_id;
'thus existing the loop for sanity check, AP will not update distributions'||
'to encumbered';
l_log_msg := 'in the loop to update encumbrance flag' ||
'for distribution table for distribution_id=' ||
to_char(t_funds_dist_tab(i).inv_distribution_id);
UPDATE ap_invoice_distributions_all aid
SET aid.encumbered_flag = 'Y'
WHERE aid.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id
AND aid.bc_event_id is not null;
UPDATE ap_self_assessed_tax_dist_all sad
SET sad.encumbered_flag = 'Y'
WHERE sad.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id
AND sad.bc_event_id is not null;
SELECT aid.bc_event_id
INTO l_bc_event_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id;
SELECT xe.event_status_code
INTO l_bc_event_status
FROM xla_events xe
WHERE xe.application_id = 200
AND xe.event_id = l_bc_event_id;
'thus existing the loop for sanity check, AP will not update distributions'||
'to encumbered';
| We need to do clean up - update the invoice |
| distributions packetid and encumbered flag |
| should be SUCCESS and ADVISORY |
+-----------------------------------------------------------------*/
IF l_count_unproc = 0 THEN
IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
l_log_msg := 'none of the BC events for the distributions returned as Successfully '||
'encumbered by PSA were unprocessed';
l_log_msg := 'update encumbered flag for distribution id=' ||
to_char(t_funds_dist_tab(i).inv_distribution_id);
UPDATE ap_invoice_distributions_all aid
SET aid.encumbered_flag = 'Y'
WHERE aid.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id
AND aid.bc_event_id is not null;
UPDATE ap_self_assessed_tax_dist_all sad
SET sad.encumbered_flag = 'Y'
WHERE sad.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id
AND sad.bc_event_id is not null;
SELECT D.dist_code_combination_id
INTO p_erv_ccid
FROM ap_invoice_distributions D
WHERE D.related_id = p_related_id
AND D.line_type_lookup_code = 'ERV';
| p_update_line_num: Variable to contain the distribution parent line
| number of the invoice that the qv should be
| applied to
| p_update_dist_num: Variable to contain the distribution line number
| of the invoice that the qv should be applied to
| 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 Calc_QV(
p_invoice_id IN NUMBER,
p_po_dist_id IN NUMBER,
p_inv_currency_code IN VARCHAR2,
p_base_currency_code IN VARCHAR2,
p_po_price IN NUMBER,
p_po_qty IN NUMBER,
p_match_option IN VARCHAR2,
p_po_uom IN VARCHAR2,
p_item_id IN NUMBER,
p_qv IN OUT NOCOPY NUMBER,
p_bqv IN OUT NOCOPY NUMBER,
p_update_line_num IN OUT NOCOPY NUMBER,
p_update_dist_num IN OUT NOCOPY NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_old_qty_var NUMBER;
select decode(p_inv_currency_code,
p_base_currency_code,1,
nvl(PD.rate,1)), -- l_rate
-- l_accrue_on_receipt_flag
PD.accrue_on_receipt_flag,
-- l_quantity_received,
POLL.quantity_received,
-- old_qty_variance
sum(nvl(D.quantity_variance,0)),
-- 0ld_base_qty_variance
decode(p_inv_currency_code,
p_base_currency_code,1,
nvl(PD.rate,1)) * sum(nvl(d.quantity_variance,0)),
--new_qty_variance
(((sum(decode(d.match_status_flag,
'A',nvl(decode(p_match_option,
'R', (qty.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code,
p_po_uom), p_po_uom
,p_item_id)),
qty.quantity_invoiced), 0),
decode(d.invoice_id, p_invoice_id,
nvl(decode(p_match_option,
'R',
(qty.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
qty.quantity_invoiced), 0),
decode(d.match_status_flag, 'A', 0,
nvl(decode(p_match_option,
'R',
(qty.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
qty.quantity_invoiced), 0)))
)) - p_po_qty ) * p_po_price)
- sum(nvl(d.quantity_variance,0))),
-- new_base_qty_variance
decode(p_inv_currency_code,
p_base_currency_code,1,
nvl(PD.rate,1)) *
(((sum(decode(d.match_status_flag,
'A',nvl(decode(p_match_option,
'R',(qty.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code
,p_po_uom), p_po_uom
,p_item_id)),
qty.quantity_invoiced),
0),
decode(d.invoice_id, p_invoice_id,
nvl(decode(p_match_option,
'R',
(qty.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
qty.quantity_invoiced), 0),
decode(d.match_status_flag, 'A', 0,
nvl(decode(p_match_option,
'R',
(qty.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
qty.quantity_invoiced), 0)))
)) - p_po_qty ) * p_po_price)
- sum(nvl(d.quantity_variance,0))),
-- l_unapproved_qty
sum(decode(d.invoice_id, p_invoice_id,
decode(match_status_flag,
'A',0,
nvl(decode(p_match_option, 'R', (qty.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code,
p_po_uom), p_po_uom, p_item_id))
,qty.quantity_invoiced),0)),
0)),
-- l_unapproved_amount
(p_po_price * sum(decode(d.invoice_id, p_invoice_id,
decode(match_status_flag,
'A',0,
nvl(decode(p_match_option,'R',
(qty.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code,
p_po_uom), p_po_uom
, p_item_id))
,qty.quantity_invoiced),0)),
0)) )
into l_rate, --bug:1826323
l_accr_on_receipt_flag, --bug:1826323
l_qty_received, --bug:1826323
l_old_qty_var,
l_old_base_qty_var,
l_new_qty_var,
l_new_base_qty_var,
l_unapproved_qty,
l_unapproved_amt
from ap_invoice_distributions d,
(SELECT d1.Invoice_distribution_id,
d1.Invoice_ID,
decode(d1.dist_match_type,
'PRICE_CORRECTION', 0,
'AMOUNT_CORRECTION', 0,
'ITEM_TO_SERVICE_PO', 0,
'ITEM_TO_SERVICE_RECEIPT', 0,
NVL(d1.corrected_quantity, 0) +
NVL(d1.quantity_invoiced,0)
) quantity_invoiced
FROM ap_invoice_distributions_all d1) qty,
po_distributions pd,
po_line_locations poll
where pd.po_distribution_id = d.po_distribution_id
and d.po_distribution_id = p_po_dist_id
and d.line_type_lookup_code NOT IN ('NONREC_TAX','TRV','TIPV')
and d.invoice_distribution_id = qty.invoice_distribution_id
and d.invoice_id = qty.invoice_id
and poll.line_location_id = pd.line_location_id
group by decode(p_inv_currency_code,
p_base_currency_code,1,
nvl(PD.rate,1)),
pd.accrue_on_receipt_flag,
poll.quantity_received;
SELECT line_number,
dist_line_number
INTO p_update_line_num,
p_update_dist_num
FROM (SELECT nvl(invoice_line_number,0) line_number,
nvl(distribution_line_number,0) dist_line_number,
row_number() OVER (ORDER BY invoice_line_number,
distribution_line_number) R
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND po_distribution_id = p_po_dist_id
AND nvl(encumbered_flag,'N') IN ('N','H','P')
AND (match_status_flag IS NULL OR match_status_flag <> 'A')
AND dist_match_type NOT IN ('PRICE_CORRECTION',
'AMOUNT_CORRECTION',
'ITEM_TO_SERVICE_PO',
'ITEM_TO_SERVICE_RECEIPT')
AND (NVL(corrected_quantity, 0) + NVL(quantity_invoiced,0)) =
(SELECT min(NVL(corrected_quantity, 0) + NVL(quantity_invoiced,0))
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND po_distribution_id = p_po_dist_id
AND nvl(encumbered_flag,'N') IN ('N','H','P')
AND (match_status_flag IS NULL OR match_status_flag <> 'A')
AND dist_match_type NOT IN ('PRICE_CORRECTION',
'AMOUNT_CORRECTION',
'ITEM_TO_SERVICE_PO',
'ITEM_TO_SERVICE_RECEIPT')
)
)
WHERE R = 1;
SELECT line_number,
dist_line_number
INTO p_update_line_num,
p_update_dist_num
FROM (SELECT nvl(invoice_line_number,0) line_number,
nvl(distribution_line_number,0) dist_line_number,
row_number() OVER (ORDER BY invoice_line_number,
distribution_line_number) R
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND po_distribution_id = p_po_dist_id
AND nvl(encumbered_flag,'N') IN ('N','H','P')
AND (match_status_flag IS NULL OR match_status_flag <> 'A')
AND dist_match_type NOT IN ('PRICE_CORRECTION',
'AMOUNT_CORRECTION',
'ITEM_TO_SERVICE_PO',
'ITEM_TO_SERVICE_RECEIPT')
AND (NVL(corrected_quantity, 0) + NVL(quantity_invoiced,0)) =
(SELECT max(NVL(corrected_quantity, 0) + NVL(quantity_invoiced,0))
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND po_distribution_id = p_po_dist_id
AND nvl(encumbered_flag,'N') IN ('N','H','P')
AND (match_status_flag IS NULL OR match_status_flag <> 'A')
AND dist_match_type NOT IN ('PRICE_CORRECTION',
'AMOUNT_CORRECTION',
'ITEM_TO_SERVICE_PO',
'ITEM_TO_SERVICE_RECEIPT')
)
)
WHERE R = 1;
| p_update_line_num: Variable to contain the distribution parent line
| number of the invoice that the av should be
| applied to
| p_update_dist_num: Variable to contain the distribution line number
| of the invoice that the av should be applied to
| 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 Calc_AV(
p_invoice_id IN NUMBER,
p_po_dist_id IN NUMBER,
p_inv_currency_code IN VARCHAR2,
p_base_currency_code IN VARCHAR2,
p_po_amt IN NUMBER,
p_av IN OUT NOCOPY NUMBER,
p_bav IN OUT NOCOPY NUMBER,
p_update_line_num IN OUT NOCOPY NUMBER,
p_update_dist_num IN OUT NOCOPY NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_old_amt_var NUMBER;
select decode(p_inv_currency_code,
p_base_currency_code,1,
nvl(PD.rate,1)),
pd.accrue_on_receipt_flag,
poll.amount_received,
-- old_amt_variance
sum(nvl(d.amount_variance,0)),
-- 0ld_base_amt_variance
decode(p_inv_currency_code,
p_base_currency_code,1,nvl(PD.rate,1))
* sum(nvl(d.amount_variance,0)),
--new_amt_variance
((sum (decode(d.match_status_flag,
'A',nvl(d.amount,0),
decode(d.invoice_id,
p_invoice_id,nvl(d.amount,0),
0)
)
) - p_po_amt
) - sum(nvl(d.amount_variance,0))
),
-- new_base_amt_variance
decode(p_inv_currency_code,
p_base_currency_code,1,nvl(PD.rate,1))
*((sum(decode (d.match_status_flag,
'A',nvl(d.amount,0),
decode(d.invoice_id,
p_invoice_id,nvl(d.amount,0),
0)
)
)-p_po_amt
) - sum(nvl(d.amount_variance,0))
),
-- l_unapproved_amount
sum(decode(d.invoice_id,
p_invoice_id,decode(match_status_flag,
'A',0,nvl(d.amount,0)
),
0)
)
into l_rate,
l_accr_on_receipt_flag,
l_amt_received,
l_old_amt_var,
l_old_base_amt_var,
l_new_amt_var,
l_new_base_amt_var,
l_unapproved_amt
from ap_invoice_distributions d,
po_distributions pd,
po_line_locations poll
where pd.po_distribution_id = d.po_distribution_id
and d.po_distribution_id = p_po_dist_id
and poll.line_location_id = pd.line_location_id
and d.line_type_lookup_code IN ('ITEM','ACCRUAL') --bugfix:3881673
group by decode(p_inv_currency_code,
p_base_currency_code,1,
nvl(PD.rate,1)),
pd.accrue_on_receipt_flag, poll.amount_received;
select nvl(distribution_line_number,0),
nvl(invoice_line_number,0)
into p_update_dist_num,
p_update_line_num
from ap_invoice_distributions
where (invoice_line_number, distribution_line_number) =
(select nvl(min(invoice_line_number),0), nvl(min(distribution_line_number),0)
from ap_invoice_distributions
where invoice_id = p_invoice_id
and po_distribution_id = p_po_dist_id
and nvl(encumbered_flag,'N') in ('N','H','P')
and (match_status_flag is null or
match_status_flag <> 'A')
and amount =
(select min(amount)
from ap_invoice_distributions
where invoice_id = p_invoice_id
and po_distribution_id = p_po_dist_id
and nvl(encumbered_flag,'N') in ('N','H','P')
and (match_status_flag is null or
match_status_flag <> 'A')) )
and (match_status_flag is null or match_status_flag <> 'A')
and invoice_id = p_invoice_id
and po_distribution_id = p_po_dist_id
and rownum < 2;
select nvl(distribution_line_number,0),
invoice_line_number
into p_update_dist_num,
p_update_line_num
from ap_invoice_distributions
where (invoice_line_number, distribution_line_number) =
(select nvl(min(invoice_line_number),0), nvl(min(distribution_line_number),0)
from ap_invoice_distributions
where invoice_id = p_invoice_id
and po_distribution_id = p_po_dist_id
and nvl(encumbered_flag,'N') in ('N','H','P')
and (match_status_flag is null or
match_status_flag <> 'A')
and amount =
(select max(amount)
from ap_invoice_distributions
where invoice_id = p_invoice_id
and po_distribution_id = p_po_dist_id
and nvl(encumbered_flag,'N') in ('N','H','P')
and (match_status_flag is null or
match_status_flag <> 'A')) )
and (match_status_flag is null or match_status_flag <> 'A')
and invoice_id = p_invoice_id
and po_distribution_id = p_po_dist_id
and rownum < 2;
SELECT AI.invoice_id, -- invoice_id
AI.invoice_num, -- invoice_num
AI.legal_entity_id, -- BCPSA bug
AI.invoice_type_lookup_code, -- invoice_type_code
AID.invoice_line_number, -- inv_line_num
AID.invoice_distribution_id , -- inv_distribution_id
AID.accounting_date, -- accounting_date
AID.LINE_TYPE_LOOKUP_CODE, -- distribution_type
AID.amount, -- distribution_amount
AID.set_of_books_id, -- set_of_books_id
AID.bc_event_id, -- bc_event_id
AID.org_id, -- org_id
NULL, --result_code
NULL, --status_code
'N' self_assessed_flag --self_assessed_flag --bug7109594
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai,
ap_invoice_lines_all ail,
gl_period_statuses per
WHERE ai.invoice_id = p_invoice_id
AND aid.invoice_id = ai.invoice_id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND (p_dist_line_num IS NULL OR
(p_dist_line_num IS NOT NULL
AND aid.distribution_line_number = p_dist_line_num))
AND ( p_inv_line_num IS NULL OR
(p_inv_line_num IS NOT NULL
AND aid.invoice_line_number = p_inv_line_num))
AND nvl(aid.encumbered_flag, 'N') in ('N', 'H', 'P')
AND aid.posted_flag in ('N', 'P')
AND ail.line_type_lookup_code NOT IN ('AWT')
AND aid.period_name = per.period_name
AND per.set_of_books_id = ai.set_of_books_id
AND per.application_id = 200
AND nvl(per.adjustment_period_flag, 'N') = 'N'
AND aid.po_distribution_id is NULL
UNION ALL
SELECT AI.invoice_id, -- invoice_id
AI.invoice_num, -- invoice_num
AI.legal_entity_id, -- BCPSA bug
AI.invoice_type_lookup_code, -- invoice_type_code
AID.invoice_line_number, -- inv_line_num
AID.invoice_distribution_id , -- inv_distribution_id
AID.accounting_date, -- accounting_date
AID.LINE_TYPE_LOOKUP_CODE, -- distribution_type
AID.amount, -- distribution_amount
AID.set_of_books_id, -- set_of_books_id
AID.bc_event_id, -- bc_event_id
AID.org_id, -- org_id
NULL, -- result_code
NULL, -- status_code
'N' self_assessed_flag -- self_assessed_flag --bug7109594
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai,
ap_invoice_lines_all ail,
gl_period_statuses per,
po_distributions_all pod
WHERE ai.invoice_id = p_invoice_id
AND aid.invoice_id = ai.invoice_id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND (p_dist_line_num IS NULL OR
(p_dist_line_num IS NOT NULL
AND aid.distribution_line_number = p_dist_line_num))
AND ( p_inv_line_num IS NULL OR
(p_inv_line_num IS NOT NULL
AND aid.invoice_line_number = p_inv_line_num))
AND ( (aid.line_type_lookup_code = 'ITEM' AND
NVL(pod.accrue_on_receipt_flag,'N') <> 'Y')
OR
(aid.line_type_lookup_code NOT IN
( 'RETAINAGE', 'ACCRUAL','ITEM' )) )
AND nvl(aid.encumbered_flag, 'N') in ('N', 'H', 'P')
AND aid.posted_flag in ('N', 'P')
AND ail.line_type_lookup_code NOT IN ('AWT')
AND aid.period_name = per.period_name
AND per.set_of_books_id = ai.set_of_books_id
AND per.application_id = 200
AND nvl(per.adjustment_period_flag, 'N') = 'N'
AND aid.po_distribution_id is not NULL
AND aid.po_distribution_id = pod.po_distribution_id
AND NOT EXISTS ( select 'Advance Exists'
from po_distributions_all pod,
po_headers_all poh,
ap_invoice_distributions_all ainvd,
ap_invoices_all ainv,
po_doc_style_headers pdsa
where pod.po_distribution_id = aid.po_distribution_id
and poh.po_header_id = pod.po_header_id
and poh.style_id = pdsa.style_id
and ainv.invoice_id = ai.invoice_id
and ainv.invoice_id = ainvd.invoice_id
and ainvd.po_distribution_id = pod.po_distribution_id
and nvl(pdsa.advances_flag, 'N') = 'Y'
and (ainvd.line_type_lookup_code = 'PREPAY'
OR
ainv.invoice_type_lookup_code = 'PREPAYMENT') )
UNION ALL
SELECT AI.invoice_id, -- invoice_id
AI.invoice_num, -- invoice_num
AI.legal_entity_id, -- BCPSA bug
AI.invoice_type_lookup_code, -- invoice_type_code
T.invoice_line_number, -- inv_line_num
T.invoice_distribution_id , -- inv_distribution_id
T.accounting_date, -- accounting_date
T.LINE_TYPE_LOOKUP_CODE, -- distribution_type
T.amount, -- distribution_amount
T.set_of_books_id, -- set_of_books_id
T.bc_event_id, -- bc_event_id
T.org_id, -- org_id
NULL, --result_code
NULL, --status_code
T.self_assessed_flag --self_assessed_flag --bug7109594
FROM ap_self_assessed_tax_dist_all t,
ap_invoices_all ai,
gl_period_statuses per
WHERE ai.invoice_id = p_invoice_id
AND t.invoice_id = ai.invoice_id
AND (p_inv_line_num IS NULL OR
(p_inv_line_num IS NOT NULL
AND t.invoice_line_number = p_inv_line_num))
AND (p_dist_line_num IS NULL OR
(p_dist_line_num IS NOT NULL
AND t.distribution_line_number = p_dist_line_num))
AND nvl(t.encumbered_flag, 'N') in ('N', 'H', 'P')
AND t.posted_flag in ('N', 'P')
AND t.period_name = per.period_name
AND per.set_of_books_id = ai.set_of_books_id
AND per.application_id = 200
AND nvl(per.adjustment_period_flag, 'N') = 'N'
AND t.po_distribution_id is NULL
UNION ALL
SELECT AI.invoice_id, -- invoice_id
AI.invoice_num, -- invoice_num
AI.legal_entity_id, -- BCPSA bug
AI.invoice_type_lookup_code, -- invoice_type_code
T.invoice_line_number, -- inv_line_num
T.invoice_distribution_id , -- inv_distribution_id
T.accounting_date, -- accounting_date
T.LINE_TYPE_LOOKUP_CODE, -- distribution_type
T.amount, -- distribution_amount
T.set_of_books_id, -- set_of_books_id
T.bc_event_id, -- bc_event_id
T.org_id, -- org_id
NULL, -- result_code
NULL, -- status_code
T.self_assessed_flag -- self_assessed_flag --bug7109594
FROM ap_self_assessed_tax_dist_all t,
ap_invoices_all ai,
gl_period_statuses per
WHERE ai.invoice_id = p_invoice_id
AND t.invoice_id = ai.invoice_id
AND (p_inv_line_num IS NULL OR
(p_inv_line_num IS NOT NULL
AND t.invoice_line_number = p_inv_line_num))
AND (p_dist_line_num IS NULL OR
(p_dist_line_num IS NOT NULL
AND t.distribution_line_number = p_dist_line_num))
AND nvl(t.encumbered_flag, 'N') in ('N', 'H', 'P')
AND t.posted_flag in ('N', 'P')
AND t.period_name = per.period_name
AND per.set_of_books_id = ai.set_of_books_id
AND per.application_id = 200
AND nvl(per.adjustment_period_flag, 'N') = 'N'
AND t.po_distribution_id is NOT NULL
AND NOT EXISTS ( select 'Advance Exists'
from po_distributions_all pod,
po_headers_all poh,
ap_invoice_distributions_all ainvd,
ap_invoices_all ainv,
po_doc_style_headers pdsa
where pod.po_distribution_id = t.po_distribution_id
and poh.po_header_id = pod.po_header_id
and poh.style_id = pdsa.style_id
and ainv.invoice_id = t.invoice_id
and ainv.invoice_id = ainvd.invoice_id
and ainvd.po_distribution_id = pod.po_distribution_id
and nvl(pdsa.advances_flag, 'N') = 'Y'
and (ainvd.line_type_lookup_code = 'PREPAY'
OR
ainv.invoice_type_lookup_code = 'PREPAYMENT') );
l_log_msg := 'Selecting Org_Id for determining Encumbrance Enabled or not' ;
SELECT org_id
INTO l_org_id
FROM AP_INVOICES_ALL
WHERE invoice_id = p_invoice_id;
| Step 1.5 - Update the encumbered_flag for recoverable tax |
| distributions to R so that these are not sent to PSA |
| for encumbering -- added for bug#8936952 |
+-----------------------------------------------------------------*/
IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
l_log_msg := ' Update encumbered flag of recoverable ' ||
'tax distributions to R';
Update ap_invoice_distributions_all
set encumbered_flag = 'R'
where invoice_id = p_invoice_id
and line_type_lookup_code = 'REC_TAX';
| Step 2 - Get all the selected distributions for processing |
+-----------------------------------------------------------------*/
IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
l_log_msg := 'Step 2 - Open FundsCntrl_Inv_Dist_Cur Cursor';
SELECT INVOICE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
REQUESTER_ID,
DESCRIPTION,
LINE_SOURCE,
ORG_ID,
LINE_GROUP_NUMBER,
INVENTORY_ITEM_ID,
ITEM_DESCRIPTION,
SERIAL_NUMBER,
MANUFACTURER,
MODEL_NUMBER,
WARRANTY_NUMBER,
GENERATE_DISTS,
MATCH_TYPE,
DISTRIBUTION_SET_ID,
ACCOUNT_SEGMENT,
BALANCING_SEGMENT,
COST_CENTER_SEGMENT,
OVERLAY_DIST_CODE_CONCAT,
DEFAULT_DIST_CCID,
PRORATE_ACROSS_ALL_ITEMS,
ACCOUNTING_DATE,
PERIOD_NAME ,
DEFERRED_ACCTG_FLAG ,
DEF_ACCTG_START_DATE ,
DEF_ACCTG_END_DATE,
DEF_ACCTG_NUMBER_OF_PERIODS,
DEF_ACCTG_PERIOD_TYPE ,
SET_OF_BOOKS_ID,
AMOUNT,
BASE_AMOUNT,
ROUNDING_AMT,
QUANTITY_INVOICED,
UNIT_MEAS_LOOKUP_CODE ,
UNIT_PRICE,
WFAPPROVAL_STATUS,
DISCARDED_FLAG,
ORIGINAL_AMOUNT,
ORIGINAL_BASE_AMOUNT ,
ORIGINAL_ROUNDING_AMT ,
CANCELLED_FLAG ,
INCOME_TAX_REGION,
TYPE_1099 ,
STAT_AMOUNT ,
PREPAY_INVOICE_ID ,
PREPAY_LINE_NUMBER ,
INVOICE_INCLUDES_PREPAY_FLAG ,
CORRECTED_INV_ID ,
CORRECTED_LINE_NUMBER ,
PO_HEADER_ID,
PO_LINE_ID ,
PO_RELEASE_ID ,
PO_LINE_LOCATION_ID ,
PO_DISTRIBUTION_ID,
RCV_TRANSACTION_ID,
FINAL_MATCH_FLAG,
ASSETS_TRACKING_FLAG ,
ASSET_BOOK_TYPE_CODE ,
ASSET_CATEGORY_ID ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
EXPENDITURE_ITEM_DATE ,
EXPENDITURE_ORGANIZATION_ID ,
PA_QUANTITY, PA_CC_AR_INVOICE_ID ,
PA_CC_AR_INVOICE_LINE_NUM ,
PA_CC_PROCESSED_CODE ,
AWARD_ID,
AWT_GROUP_ID ,
REFERENCE_1 ,
REFERENCE_2 ,
RECEIPT_VERIFIED_FLAG ,
RECEIPT_REQUIRED_FLAG ,
RECEIPT_MISSING_FLAG ,
JUSTIFICATION ,
EXPENSE_GROUP ,
START_EXPENSE_DATE ,
END_EXPENSE_DATE ,
RECEIPT_CURRENCY_CODE ,
RECEIPT_CONVERSION_RATE,
RECEIPT_CURRENCY_AMOUNT ,
DAILY_AMOUNT ,
WEB_PARAMETER_ID ,
ADJUSTMENT_REASON ,
MERCHANT_DOCUMENT_NUMBER ,
MERCHANT_NAME ,
MERCHANT_REFERENCE ,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID ,
COUNTRY_OF_SUPPLY,
CREDIT_CARD_TRX_ID ,
COMPANY_PREPAID_INVOICE_ID,
CC_REVERSAL_FLAG ,
CREATION_DATE ,
CREATED_BY,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE,
REQUEST_ID ,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
--ETAX: Invwkb
INCLUDED_TAX_AMOUNT,
PRIMARY_INTENDED_USE,
--Bugfix:4673607
APPLICATION_ID,
PRODUCT_TABLE,
REFERENCE_KEY1,
REFERENCE_KEY2,
REFERENCE_KEY3,
REFERENCE_KEY4,
REFERENCE_KEY5,
--bugfix:4674194
SHIP_TO_LOCATION_ID,
--bug 7022001
PAY_AWT_GROUP_ID
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND line_number = nvl(p_line_number,line_number)
--Invoice Lines: Distributions
ORDER BY decode(line_type_lookup_code,'ITEM',1,2), line_number;
SELECT batch_id,
vendor_id,
invoice_date,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date
INTO l_batch_id,
l_vendor_id,
l_invoice_date,
l_invoice_currency_code,
l_exchange_rate,
l_exchange_rate_type,
l_exchange_date
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
SELECT line_type_lookup_code
INTO l_line_type_lookup_code
FROM ap_invoice_lines ail
WHERE ail.invoice_id = p_invoice_id
AND ail.line_number = p_invoice_line_number;