VALUE SET NULL VALUE IN ORACLE APPS

Value Set - NULL valueset

Note that a bind variable, by default, is required; that is, it must have a value for the statement, expression, or user exit which uses it to have meaning. A bind variable can be made optional by using the :NULL suffix; so that if the bind variable is NULL, the segment/parameter using it will be disabled, and its required property (if enabled) will be
ignored. The :NULL suffix is discussed at the end of this section.

:NULL suffix
-------------
Use the :NULL suffix to make your bind variable optional, that is, allow null values. Instead of :block.field, :$PROFILES$.Option_name, or :$FLEX$.Value_set_name, you would use :block.field:NULL, $PROFILES$.Option_name:NULL, or :$Flex$.Value_set_name:NULL, respectively. For example, if your value set name is Car_Maker_Name_Value_Set, you would use
:$FLEX$.Car_Maker_Name_Value_Set:NULL.

use :$FLEX$.Value_Set:NULL IS NULL expression to check if that particular value set has a NULL value

Sales Order Return Invoice Query

SELECT ref_hdr.order_number primary_so_no,
TO_CHAR (ref_hdr.ordered_date, 'DD-MON-YYYY') primary_so_date,
hdr.order_number return_so_no,
TO_CHAR (hdr.ordered_date, 'DD-MON-YYYY') return_so_date,
ref_dtl.line_number return_so_line, ract.trx_number,
TO_CHAR (ract.trx_date, 'DD-MON-YYYY') trx_date,
TO_CHAR (hdr.ordered_date, 'DD-MON-YYYY') ord_date, ot.NAME order_type,
hdr.order_category_code, party.party_name sold_to,
cust_acct.account_number customer_number, dtl.ordered_quantity,
msi.segment1 item_code, msi.description item_desc,
ship_from_org.organization_code ship_from, pl.NAME price_list,
ship_su.LOCATION ship_to, ship_su.LOCATION ship_to_location,
ship_loc.address1 ship_to_address1, ship_loc.address2 ship_to_address2,
ship_loc.address3 ship_to_address3, ship_loc.address4 ship_to_address4,
DECODE (ship_loc.city,
NULL, NULL,
ship_loc.city || ', '
)
|| DECODE (ship_loc.state,
NULL, ship_loc.province || ', ',
ship_loc.state || ', '
)
|| DECODE (ship_loc.postal_code,
NULL, NULL,
ship_loc.postal_code || ', '
)
|| DECODE (ship_loc.country, NULL, NULL, ship_loc.country)
ship_to_address5,
dtl.CONTEXT, dtl.attribute1, dtl.attribute2, dtl.attribute3,
hdr.header_id, dtl.reference_header_id, dtl.reference_line_id
FROM oe_order_headers_all hdr,
oe_order_lines_all dtl,
oe_transaction_types_tl ot,
qp_list_headers_tl pl,
hz_cust_accounts cust_acct,
hz_parties party,
mtl_parameters ship_from_org,
hz_locations ship_loc,
hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all ship_cas,
hz_party_sites ship_ps,
mtl_system_items_b msi,
oe_order_headers_all ref_hdr,
oe_order_lines_all ref_dtl,
ra_customer_trx_lines_all ractl,
ra_customer_trx_all ract
WHERE hdr.header_id = dtl.header_id
AND hdr.order_type_id = ot.transaction_type_id
AND hdr.price_list_id = pl.list_header_id(+)
AND hdr.sold_to_org_id = cust_acct.cust_account_id(+)
AND cust_acct.party_id = party.party_id(+)
AND hdr.ship_from_org_id = ship_from_org.organization_id(+)
AND hdr.ship_to_org_id = ship_su.site_use_id(+)
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
AND ship_cas.party_site_id = ship_ps.party_site_id(+)
AND ship_loc.location_id(+) = ship_ps.location_id
AND msi.inventory_item_id = dtl.inventory_item_id
AND msi.organization_id = hdr.ship_from_org_id
AND ot.NAME IN ('SKG ORDER RETURN - QTY', 'SKG ORDER RETURN WITH CM')
AND ref_hdr.header_id = dtl.reference_header_id
AND ref_dtl.line_id = dtl.reference_line_id
AND dtl.credit_invoice_line_id = ractl.customer_trx_line_id(+)
AND ract.customer_trx_id(+) = ractl.customer_trx_id
-- AND hdr.order_number = '7'
-- AND hdr.org_id = 1

R12 Customers Query

SELECT hzp.PARTY_NUMBER customer_number, hzp.party_name customer_name, hzp.party_type,
hzp.address1, hzp.address2, hzp.address3, hzp.address4, hzp.city,
hzp.postal_code, hzp.state, hzp.country, hzp.status,
hzps.party_site_number, hzac.account_number, hzac.account_name,
site.site_use_code
FROM hz_parties hzp,
hz_party_sites hzps,
hz_cust_accounts hzac,
hz_locations hzl,
hz_cust_acct_sites_all acct,
hz_cust_site_uses_all site
WHERE hzp.party_id = hzps.party_id
AND hzp.party_id = hzac.party_id
AND hzps.location_id = hzl.location_id
AND hzps.party_site_id = acct.party_site_id
AND acct.cust_account_id = hzac.cust_account_id
AND acct.cust_acct_site_id = site.cust_acct_site_id

Sales Order Invoice Query

SELECT hdr.order_number, TO_CHAR (hdr.ordered_date, 'dd-MON-yyyy') ord_date,
dtl.line_number, msi.segment1 sona_part_no,
msi.description item_desc, dtl.ordered_quantity,
(ractl.quantity_invoiced) quantity_invoiced,
ractl.attribute3 kanban_no, dtl.unit_selling_price, ract.trx_number,
TO_CHAR (ract.trx_date, 'dd-MON-yyyy') txn_date,
TO_CHAR (ractl.creation_date, 'yyyy/mm/dd') invoice_date,
TO_CHAR (ractl.creation_date, 'hh24:mm:SS') invoice_time,
rac.customer_number, rac.customer_name customer_name,
ractl.attribute1 cust_po_no, extended_amount, ractl.revenue_amount
FROM oe_order_headers_all hdr,
oe_order_lines_all dtl,
ra_customer_trx_lines_all ractl,
mtl_system_items_b msi,
ra_customer_trx_all ract,
ar_customers rac
WHERE hdr.header_id = dtl.header_id
AND hdr.org_id = dtl.org_id
AND ractl.interface_line_attribute1 = TO_CHAR (hdr.order_number)
AND ractl.sales_order = hdr.order_number
AND ractl.interface_line_attribute6 = TO_CHAR (dtl.line_id)
AND msi.inventory_item_id = dtl.inventory_item_id
AND msi.organization_id = hdr.ship_from_org_id
AND ract.customer_trx_id = ractl.customer_trx_id
AND ract.org_id = ractl.org_id
AND rac.customer_id = ract.ship_to_customer_id
AND ractl.line_type = 'LINE'
AND TRUNC (ract.creation_date) > '01-APR-2009'
--AND hdr.sold_to_org_id = ract.ship_to_customer_id
AND hdr.order_number = '10106426'
-- AND NVL (:p_trx_number, ract.trx_number) = :p_trx_number
ORDER BY hdr.order_number, hdr.ordered_date, dtl.line_number