[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
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')))
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 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')))
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 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 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';
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
SET encumbered_flag = 'Y'
WHERE invoice_distribution_id =
t_funds_dist_tab(i).inv_distribution_id
AND bc_event_id is not null ;*/
UPDATE ap_invoice_distributions_all
SET encumbered_flag = 'Y'
WHERE invoice_distribution_id =
t_funds_dist_tab(i).inv_distribution_id
AND bc_event_id is not null ;
UPDATE ap_self_assessed_tax_dist_all sad
SET encumbered_flag = 'Y'
WHERE sad.invoice_distribution_id =
t_funds_dist_tab(i).inv_distribution_id
AND sad.bc_event_id is not null ;
| We need to do clean up - update the invoice |
| distributions packetid and encumbered flag |
| should be SUCCESS and ADVISORY |
+-----------------------------------------------------------------*/
IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
l_log_msg := 'Step 6.2 - funds reserve is done fully' ||
' and process sucess return code =' || l_return_code ;
l_log_msg := 'update encumbered flag for distribution id=' ||
to_char(t_funds_dist_tab(i).inv_distribution_id);
UPDATE ap_invoice_distributions
SET encumbered_flag = 'Y'
WHERE invoice_distribution_id =
t_funds_dist_tab(i).inv_distribution_id
AND bc_event_id is not null ;*/
UPDATE ap_invoice_distributions_all
SET encumbered_flag = 'Y'
WHERE invoice_distribution_id =
t_funds_dist_tab(i).inv_distribution_id
AND bc_event_id is not null ;
UPDATE ap_self_assessed_tax_dist_all sad
SET 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', (d.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code,
p_po_uom), p_po_uom
,p_item_id)),
d.quantity_invoiced), 0),
decode(d.invoice_id, p_invoice_id,
nvl(decode(p_match_option,
'R',
(d.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
d.quantity_invoiced), 0),
decode(d.match_status_flag, 'A', 0,
nvl(decode(p_match_option,
'R',
(d.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
d.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',(d.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code
,p_po_uom), p_po_uom
,p_item_id)),
d.quantity_invoiced),
0),
decode(d.invoice_id, p_invoice_id,
nvl(decode(p_match_option,
'R',
(d.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
d.quantity_invoiced), 0),
decode(d.match_status_flag, 'A', 0,
nvl(decode(p_match_option,
'R',
(d.quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
d.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', (quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code,
p_po_uom), p_po_uom, p_item_id))
,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',
(quantity_invoiced *
po_uom_s.po_uom_convert(
nvl(d.matched_uom_lookup_code,
p_po_uom), p_po_uom
, p_item_id))
,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,
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 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 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 quantity_invoiced =
(select min(quantity_invoiced)
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 quantity_invoiced =
(select max(quantity_invoiced)
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;
| 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 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;