Home » Developer & Programmer » Reports & Discoverer » REP-1401: 'afterpform': Fatal PL/SQL error occurred. ORA-06502: PL/SQL: numeric or value error (SQL Developer & Report Builder)
REP-1401: 'afterpform': Fatal PL/SQL error occurred. ORA-06502: PL/SQL: numeric or value error [message #661135] Wed, 08 March 2017 18:22 Go to next message
nugraha.perdana2390
Messages: 4
Registered: March 2017
Junior Member
i will display parameter sql developer this logic, help me true logic

select
prp.note_to_receiver,
prp.pr_description,
prp.no_pr,
prp.pr_line,
prp.pr_item,
prp.pr_item_desc,
prp.req_line_quantity,
prp.pr_uom,
prp.need_by_date,
prp.pr_cur,
prp.pr_unit_price,
prp.pr_rate,
prp.pr_amount,
prp.pr_charge_account,
prp.pr_creation_date,
prp.approve_budget,
prp.pr_status,
prp.no_po,
prp.release_num,
prp.supplier,
prp.po_curr,
prp.unit_price_po,
prp.po_rate,
prp.po_amount,
prp.po_creation_date,
prp.po_approve_date,
rcv.receipt_num,
rcv.transaction_type,
rcv.transaction_date,
rcv.transact_qty,
prp.po_qty,
prp.quantity_cancelled,
prp.promised_date,
prp.note_to_buyer,
prp.buyer
from
(
-- PR & PO --
select
pr.note_to_receiver,
pr.description pr_description,
pr.no_pr,
pr.line_num pr_line,
pr.item pr_item,
pr.item_description pr_item_desc,
pr.req_line_quantity,
pr.unit_meas_lookup_code pr_uom,
pr.need_by_date,
pr.curr_code pr_cur,
pr.unit_price pr_unit_price,
pr.rate pr_rate,
pr.unit_price * pr.req_line_quantity * nvl(pr.rate, 1) pr_amount,
pr.code pr_charge_account,
pr.creation_date pr_creation_date,
pr.approved_date as approve_budget,
pr.authorization_status as pr_status,
po.po_header_id,
po.po_line_id,
po.line_location_id,
po.po_distribution_id,
po.no_po,
(select release_num from po_releases_all where po_headeR_id = PO.po_headeR_id and po_release_id = PO.po_release_id) release_num,
(select vendor_name from ap_suppliers where vendor_id = po.vendor_id) supplier,
po.currency_code po_curr,
po.unit_price unit_price_po,
po.rate po_rate,
po.unit_price * nvl(po.rate, 1) po_amount,
po.creation_date po_creation_date,
po.approved_date po_approve_date,
po.deliver_to_person_id,
po.quantity po_qty,
po.quantity_cancelled,
po.promised_date,
pr.note_to_agent note_to_buyer,
po.full_name buyer
from
(
select
prha.segment1 no_pr,
prha.authorization_status,
prla.parent_req_line_id,
prla.cancel_date,
nvl(prla.cancel_flag, 'N') status,
prha.approved_date,
prha.creation_date,
prha.last_update_date,
prha.description,
prla.line_num,
msi.segment1 item,
prla.item_description,
prla.unit_meas_lookup_code,
nvl(prla.currency_unit_price, prla.unit_price) unit_price,
prda.req_line_quantity,
decode(prla.currency_code, null, 'IDR', prla.currency_code) curr_code,
nvl(prla.rate, 1) rate,
prla.rate_date,
prla.rate_type,
prda.distribution_id,
prla.destination_type_code,
prla.destination_subinventory,
prha.created_by,
fnd_flex_ext.get_segs('SQLGL', 'GL#', 50368, prda.code_combination_id) code,
prla.need_by_date,
prla.note_to_receiver,
prla.note_to_agent
from
po.po_requisition_headers_all prha,
po.po_requisition_lines_all prla,
mtl_system_items_fvl msi,
po_req_distributions_all prda
where
prla.requisition_header_id = prha.requisition_header_id
and msi.organization_id = prha.org_id
and prla.org_id = msi.organization_id
and prha.org_id = prla.org_id
and prla.item_id = msi.inventory_item_id
and prla.requisition_line_id = prda.requisition_line_id
and prla.modified_by_agent_flag is null
and trunc(prha.creation_date) between :p_date_from and :p_date_to
and
(
(prla.cancel_flag = 'Y' and prla.quantity_received = 0)
or
(nvl(prla.cancel_flag, 'N') <> 'Y')
)
and prha.authorization_status = nvl(:p_status, prha.authorization_status)
) pr,
(
select
pha.segment1 no_po,
pha.currency_code,
pla.unit_price,
pha.creation_date,
pha.approved_date,
pha.vendor_id,
pda.req_distribution_id,
pda.po_release_id,
pda.po_header_id,
pda.line_location_id,
pda.po_distribution_id,
nvl(pha.rate, 1) rate,
pla.po_line_id,
pla.line_num,
pda.deliver_to_person_id,
pda.quantity_delivered,
plla.promised_date,
plla.quantity,
pda.quantity_cancelled,
pap.full_name
from
po.po_headers_all pha,
po.po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda,
per_all_people_f pap
where
pha.po_header_id = pla.po_header_id
and plla.po_line_id = pla.po_line_id
and pha.po_header_id = pda.po_header_id
and pla.po_line_id = pda.po_line_id
and plla.line_location_id = pda.line_location_id
and pap.person_id = pha.agent_id
) po,
fnd_user fu
where
pr.distribution_id = po.req_distribution_id(+)
and pr.created_by = fu.user_id
and fu.user_name = :p_user
) prp,
-- Receiving --
(
select
rvt.receipt_num,
rvt.transaction_type,
rvt.transaction_date,
rvt.transact_qty,
rvt.po_header_id,
rvt.po_line_id,
rvt.po_line_location_id,
rvt.po_distribution_id,
rvt.deliver_to_person_id
from rcv_vrc_txs_vendint_v rvt
where
rvt.shipment_header_id not in
(
select distinct rsh.shipment_header_id
from
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt
where
rsh.shipment_header_id = rsl.shipment_header_id
and rsl.shipment_line_id = rt.shipment_line_id
and rt.transaction_type = 'CORRECT'

)
) rcv
where
prp.po_header_id = rcv.po_header_id(+)
and prp.po_line_id = rcv.po_line_id(+)
and prp.line_location_id = rcv.po_line_location_id(+)
and prp.po_distribution_id = rcv.po_distribution_id(+)
and prp.deliver_to_person_id = rcv.deliver_to_person_id(+)

order by no_pr, pr_line;

I have
where
case when rcv.receipt_num is not null and :p_receive_num = 'Y' then rcv.receipt_num
when rcv.receipt_num is not null or rcv.receipt_num is null then :p_receive_num = 'N'
else rcv.receipt_num end = :p_receive_num}

I have the partameter if :p_receive_num ='Y' then just rcv.receipt_num is not null is displayed .
if :p_receive_num = 'N' then rcv.receipt_num is not null or rcv.receipt_num is null is displayed,
Re: REP-1401: 'afterpform': Fatal PL/SQL error occurred. ORA-06502: PL/SQL: numeric or value error [message #661141 is a reply to message #661135] Thu, 09 March 2017 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: REP-1401: 'afterpform': Fatal PL/SQL error occurred. ORA-06502: PL/SQL: numeric or value error [message #661154 is a reply to message #661135] Thu, 09 March 2017 03:47 Go to previous message
cookiemonster
Messages: 12929
Registered: September 2008
Location: Rainy Manchester
Senior Member
nugraha.perdana2390 wrote on Thu, 09 March 2017 00:22
then :p_receive_num = 'N'
That's a boolean expression, case can't return boolean expressions. I suspect that should just be:

then 'N'

since you're comparing the result of the case to :p_receive_num
Previous Topic: How to create web layout from my paper layout
Next Topic: afterpform': Fatal PL/SQL error occurred, PL/SQL: numeric or value error (2 threads merged by bb)
Goto Forum:
  


Current Time: Tue Nov 21 20:01:11 CST 2017

Total time taken to generate the page: 0.01965 seconds