[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT aid.distribution_line_number FII_AP_DIST_NUM,
alc.displayed_field FII_AP_DIST_TYPE,
aid.amount FII_AP_DIST_AMOUNT,
apps.fnd_flex_ext.get_segs(''SQLGL'',''GL#'',glcc.chart_of_accounts_id,aid.dist_code_combination_id) FII_AP_ACCOUNT,
fii_ap_detail.get_account_desc(glcc.chart_of_accounts_id,aid.dist_code_combination_id) FII_AP_ACCOUNT_DESC,
sum (aid.amount) over() FII_AP_DIST_AMOUNT_GT
FROM ap_invoice_distributions_all aid,
ap_lookup_codes alc,
gl_code_combinations glcc
WHERE aid.line_type_lookup_code = alc.lookup_code
AND alc.lookup_type = ''INVOICE DISTRIBUTION TYPE''
AND aid.dist_code_combination_id = glcc.code_combination_id
AND aid.invoice_id = :INVOICE_ID'||l_line_stmt||'
&ORDER_BY_CLAUSE
';
SELECT hold.hold_lookup_code FII_MEASURE1
,TRUNC(hold.hold_date) FII_MEASURE2
,DECODE(hold.release_lookup_code, NULL, NULL,hold.last_update_date) FII_MEASURE4
,fnd_usr.user_name FII_MEASURE3
FROM ap_invoices_all inv
,ap_holds_all hold
,fnd_user_view fnd_usr
WHERE inv.invoice_id = hold.invoice_id
AND hold.invoice_id = :INVOICE_ID
AND hold.held_by = fnd_usr.user_id
AND inv.cancelled_date IS NULL
AND inv.invoice_type_lookup_code NOT IN (''PREPAYMENT'')
&ORDER_BY_CLAUSE
';
sqlstmt := 'select action FII_MEASURE1,
action_date FII_MEASURE2,
usr.user_name FII_MEASURE3
from
(select :ENTRY action,
entered_date action_date,
created_by by_whom
from fii_ap_invoice_b
where invoice_id=:INVOICE_ID
union all
select :HOLD_PLACED action,
hold_date action_date,
held_by by_whom
from fii_ap_inv_holds_b
where invoice_id=:INVOICE_ID
and period_type_id = 1
union all
select :HOLD_RELEASED action,
release_date action_date,
released_by by_whom
from fii_ap_inv_holds_b
where invoice_id=:INVOICE_ID
and period_type_id = 1
union all
SELECT CASE WHEN b.amount < 0 THEN
:PREPAY_APPLIED
ELSE
:PREPAY_UNAPPLIED
END action,
trunc(b.creation_date) action_date,
a.last_updated_by by_whom
from ap_invoice_distributions_all a, ap_invoice_distributions_all b
where a.invoice_id=:INVOICE_ID
and a.invoice_distribution_id = b.prepay_distribution_id
and b.line_type_lookup_code = ''PREPAY''
and b.amount <> 0
and a.invoice_id <> b.invoice_id
union all
select :PAYMT action,
action_date action_date,
created_by by_whom
from fii_ap_pay_sched_b
where invoice_id=:INVOICE_ID
and action in (''PAYMENT'', ''PREPAYMENT'')
and period_type_id = 1
union all
select CASE WHEN c.stopped_date is not null
THEN :PAYMT_STOP
ELSE :PAYMT_RELEASE END action,
CASE WHEN c.stopped_date is not null
THEN c.stopped_date
ELSE c.released_date END action_date,
CASE WHEN c.stopped_date is not null
THEN c.stopped_by
ELSE c.released_by END by_whom
from ap_checks_all c, ap_invoice_payments_all p
where c.check_id = p.check_id
and p.invoice_id = :INVOICE_ID
and (c.stopped_date is not null
OR c.released_date is not null)
) a,
fnd_user usr
Where a.by_whom = usr.user_id
&ORDER_BY_CLAUSE ';
sqlstmt := 'select a.Payment_Num FII_MEASURE1,
a.due_date FII_MEASURE2,
a.Gross_Amount FII_MEASURE3,
to_char(discount_date,'''||l_date_mask||''') FII_MEASURE4,
a.Discount_Amount_Available FII_MEASURE5,
to_char(second_discount_date,'''||l_date_mask||''') FII_MEASURE6,
a.Second_Disc_Amt_Available FII_MEASURE7,
to_char(third_discount_date, '''||l_date_mask||''') FII_MEASURE8,
a.Third_Disc_Amt_Available FII_MEASURE9,
decode(nvl(a.hold_flag, ''N''), ''Y'', '''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE10,
sum(a.gross_amount) over() FII_MEASURE11
from AP_Payment_Schedules_All a
where a.invoice_id = :INVOICE_ID
&ORDER_BY_CLAUSE '
;
SELECT ail.line_number FII_AP_LINE_NUM,
alc.displayed_field FII_AP_LINE_TYPE,
ail.amount FII_AP_LINE_AMOUNT,
ail.description FII_AP_LINE_DESC,
ail.quantity_invoiced FII_AP_QUANTITY,
muom.unit_of_measure_tl FII_AP_UOM, -- muom.uom_code
poh.segment1 FII_AP_PO_NUM, -- poh.segment1
poll.shipment_num FII_AP_PO_SHIPMENT_NUM,
por.release_num FII_AP_RELEASE_NUM,
rcvsh.receipt_num FII_AP_RECEIPT_NUM, -- receipt_num
sum (ail.amount) over() FII_AP_GT_LINE_AMOUNT,
-- Drill from Line Amount column to Invoice Distributions Detail report
''' || l_url_line_amount || ''' FII_AP_LINE_AMOUNT_DRILL,
-- Drill from PO Number column to PO Overview report
''' || l_url_po_number || ''' FII_AP_PO_NUM_DRILL
FROM ap_invoice_lines_all ail,
ap_lookup_codes alc,
mtl_units_of_measure muom,
po_headers_all poh,
po_line_locations_all poll,
po_releases_all por,
rcv_transactions rcvt,
rcv_shipment_headers rcvsh
WHERE ail.line_type_lookup_code = alc.lookup_code
AND alc.lookup_type = ''INVOICE LINE TYPE''
AND ail.unit_meas_lookup_code = muom.unit_of_measure(+)
AND ail.po_header_id = poh.po_header_id(+)
AND ail.po_line_location_id = poll.line_location_id(+)
AND ail.po_release_id = por.po_release_id(+)
AND ail.rcv_transaction_id = rcvt.transaction_id(+)
AND rcvt.shipment_header_id = rcvsh.shipment_header_id(+)
AND ail.invoice_id = :INVOICE_ID
ORDER BY ail.line_number';