ORACLE FUSION CLOUD: April 2020

Wednesday, April 29, 2020

Oracle apps r12 invoiced quantity variance with the ordered quantity OM: Over Shipment Tolerance

Oracle apps r12 invoiced quantity variance with the ordered quantity

Invoice Interface code looks at the tolerances for lines over shipped

profile stores OM: Over Shipment Tolerance

If we over shipped then we will see invoiced quantity as (ordered quantity + (ordered quantity * ship tolerance above)).
For example:
OM: Over Shipment Tolerance is 50 %
Ordered quantity = 10
Shipped Quantity = 15
Invoice Quantity = (10 + (10 * .50)) = 15

One more example
OM: Over Shipment Tolerance is 0 % (please find the below screenshot)
Ordered quantity = 1 and 1
Shipped Quantity = 8 and 4
Invoice Quantity =(ordered quantity + (ordered quantity * ship tolerance above)).

Line 11= (1 + (1 * 0) = 1

Line 12= (1 + (1 * 0) = 1

Monday, April 20, 2020

Oracle BI Publisher - Leading zeros truncated for excel reports - format numbers as text

When you want to display some data which has leading zeros in EXCEL output you will not get the desired output. But in PDF it will come as what you are expecting. This is not with the issue of your data. This is due to the unique nature of the EXCEL cell format. When you are trying to put a text data in a cell without making any change to cell format it will treat as the number and it will truncate all leading zeros and all trailing zeros after '.' . So what you have to do is to convert that data into a format that EXCEL can treat as text.

1. In the rtf template, add two spaces before the column.

2. Use the following xml tag

<fo:bidi-override direction=”ltr” unicode-bidi=”bidi-override”> <?format-number:INVOICE_NUMBER;’09999999′?> </fo:bidi-override>”

3. Instead of the using the tag < ? INVOICE_NUMBER? >
use =”<?INVOICE_NUMBER?>”.

Excel treat value as character and doesn’t trim the leading zeors of value.

How to Find the Link Between Transaction Number and Receipt Number in AR

ELECT  ACR.RECEIPT_NUMBER RECEIPT_NO,RCT.TRX_NUMBER INVOICE_NO
FROM    AR_RECEIVABLE_APPLICATIONS_ALL ARA,
        AR_CASH_RECEIPTS_ALL           ACR,
        RA_CUSTOMER_TRX_ALL            RCT
WHERE   ARA.STATUS='APP'
  AND   ARA.CASH_RECEIPT_ID=ACR.CASH_RECEIPT_ID
  AND   ARA.APPLIED_CUSTOMER_TRX_ID=RCT.CUSTOMER_TRX_ID

Select distinct acra.cash_receipt_id , araa.applied_payment_schedule_id ,
acra.amount ,
acra.status , acra.org_id ,acra.receipt_number , aps.status apsstatus ,
aps.customer_id ,aps.customer_trx_id ,
aps.amount_applied ,aps.amount_due_original ,aps.amount_due_remaining
,aps.customer_site_use_id ,
aps.cash_receipt_status_last from ar_cash_receipts_all acra ,
ar_payment_schedules_all aps ,
ar_receivable_applications_all araa, ra_customer_trx_all rcta ,
ra_customer_trx_lines_all rctla
where aps.cash_receipt_id = acra.cash_receipt_id
and acra.customer_site_use_id = aps.customer_site_use_id
andaraa.cash_receipt_id = acra.cash_receipt_id
*--and acra.status = 'APP'*
and araa.applied_payment_schedule_id is not null
and aps.class = 'PMT'
and acra.status != 'REV'

Thursday, April 16, 2020

Sales order query ,Order_amount , invoice_amount oracle apps r12 sql query to find order amount

select ooh.ORDER_NUMBER "Order Number"

, ra.CUSTOMER_NUMBER "Customer Number"

, ra.CUSTOMER_NAME "Customer Name"

,wnd.SOURCE_HEADER_ID "Delivery Number"

,ool.LINE_ID "Order LineID"

,ool.LINE_NUMBER "Order Line Number"

,rct.TRX_NUMBER "Invoice Number"

,rct.TRX_DATE "Invoice Date"

,ool.ORDERED_QUANTITY "Ordered Quantity"

,ool.SHIPPED_QUANTITY "Shipped Quantity"

,rctl.QUANTITY_INVOICED "Invoiced Quantity"

,ool.ORDERED_QUANTITY*ool.UNIT_SELLING_PRICE "Ordered Amount"

,rctl.QUANTITY_INVOICED*rctl.UNIT_SELLING_PRICE "Invoiced Amount"

,(select sum(l.ORDERED_QUANTITY*l.UNIT_SELLING_PRICE)

from oe_order_headers_all h

,oe_order_lines_all l

where h.HEADER_ID in l.HEADER_ID

and h.HEADER_ID=ooh.HEADER_ID

group by h.HEADER_ID ) "Sub Total"

from oe_order_headers_all ooh

,oe_order_lines_all ool

,ra_customers ra

,wsh_new_deliveries wnd

,ra_customer_trx_all rct

,ra_customer_trx_lines_all rctl

where 1=1

and ooh.ORDER_NUMBER = '66415'

and ooh.HEADER_ID = ool.HEADER_ID

and ra.CUSTOMER_ID = ooh.SOLD_TO_ORG_ID

and ooh.HEADER_ID = wnd.SOURCE_HEADER_ID(+)

--and ra.CUSTOMER_ID = wnd.CUSTOMER_ID

and to_char(ooh.ORDER_NUMBER) = rct.CT_REFERENCE

and rct.SOLD_TO_CUSTOMER_ID = ra.CUSTOMER_ID

and rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID

and rctl.SHIP_TO_CUSTOMER_ID = ra.CUSTOMER_ID

group by ooh.HEADER_ID

,ooh.ORDER_NUMBER

, ra.CUSTOMER_NUMBER

, ra.CUSTOMER_NAME

,wnd.SOURCE_HEADER_ID

,ool.LINE_ID

,ool.LINE_NUMBER

,rct.TRX_NUMBER

,rct.TRX_DATE

,ool.ORDERED_QUANTITY

,ool.SHIPPED_QUANTITY

,rctl.QUANTITY_INVOICED

,ool.ORDERED_QUANTITY*ool.UNIT_SELLING_PRICE

,rctl.QUANTITY_INVOICED*rctl.UNIT_SELLING_PRICE

ORA-01732: data manipulation operation not legal on this view

ORA-01732: data manipulation operation not legal on this view

Question: What causes the ORA-01732 error?

ORA-01732: data manipulation operation not legal on this view.

Answer: The oerr utility shows this for the ORA-01732 error, showing that you cannot issue any update DML against a view:

ORA-01732: data manipulation operation not legal on this view

Cause: An attempt was made to use an UPDATE, INSERT, or DELETE statement on a view that contains expressions or functions or was derived from more than one table. If a join operation was used to create the view or the view contains virtual columns derived from functions or expressions, then the view may only be queried.

Action: UPDATE, INSERT, or DELETE rows in the base tables instead and restrict the operations on the view to queries.

Instead, you need to find the base tables from the view and issue the updates against the base tables. You cannot update a view except with INSTEAD OF triggers if the defining query of the view contains one of the following constructs:

A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions . . .
As we see, the ORA-01732 can be fixed by issuing the DML against the base tables.

Oracle Training from Don Burleson

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Monday, April 13, 2020

Bored at Home? Here's a Massive List of Museums, Zoos, and Theme Parks Offering Virtual Tours

The effects of social distancing have left many feeling, well, a little stir-crazy. Though we all try to stay busy by keeping active and working from home during the COVID-19 outbreak, it's easy to fall into a rut. Luckily, there's more than one way to stave off boredom while staying indoors. Instead of binging the best of what Netflix has to offer for the second time, it's time to broaden your horizons with some virtual tours.

"Walk" through some of the world's most prestigious cultural institutions, like The Met and The American Museum of Natural History, then go for a "ride" on Disney's new Frozen rollercoaster with the kids. And regardless of your age, we think everyone will enjoy some live footage of pandas playing at the zoo!

Museums

The Louvre: You don't have to book a ticket to Paris to check out some of the famous pieces in the world's largest art museum. The Louvre has free online tours of three famous exhibits, including Egyptian Antiquities.
Solomon R. Guggenheim Museum: The works of Pablo Picasso, Piet Mondrian, Jeff Koons, and Franz Marc are just some of the 625 artists whose work are a part of the Guggenheim's Collection Online.
Smithsonian National Museum of Natural History: Move at your own pace through the 360-degree room-by-room tour of every exhibit in the museum.
Van Gogh Museum: You can get up close and personal with the impressionist painter's most famous work thanks to Google Arts & Culture.
Getty Museum: Los Angeles's premiere gallery has two virtual tours, including "Eat, Drink, and Be Merry," which is a closer look at food in the Middle Ages and Renaissance.
The Vatican Museum: The Sistine Chapel, St. Peter's Basilica, and Raphael's Room, are just some of the sites you can see on the Vatican's virtual tour.
Thyssen-Bornemisza Museum: Madrid's must-see art museum has the works of some of the continent's most celebrated artists like Rembrandt and Dali available online.
Georgia O’Keeffe Museum: Six virtual exhibits are available online from this museum named for the "Mother of American modernism."
National Museum of Anthropology, Mexico City: Dive into the pre-Hispanic history of Mexico with 23 exhibit rooms full of Mayan artifacts.
British Museum, London: The Rosetta Stone and Egyptian mummies are just a couple of things that you're able to see on a virtual tour of the museum.
NASA: Both Virginia's Langley Research Center and Ohio's Glenn Research Center offer online tours for free. Also, you can try some "augmented reality experiences" via The Space Center Houston's app.
National Women's History Museum: Have a late International Women's Day celebration with online exhibits and oral histories from the Virginia museum.
Metropolitan Museum of Art: Though the Met Gala was cancelled this year, you can still have a peak at the The Costume Institute Conversation Lab, which is one of the institution's 26 online exhibits.
High Museum of Art, Atlanta: This museum's popular online exhibits include "Civil Rights Photography" — photos that capture moments of social protest like the Freedom Rides and Rosa Park's arrest.
Detroit Institute of Arts: Mexican art icon Frida Kahlo is the focal point of two of the four available online exhibits.
Rijksmuseum, Amsterdam: The Golden Age of Dutch art is highlighted in this museum which includes the work of Vermeer and Rembrandt.
National Museum of the United States Air Force: You can't take a ride in Franklin D. Roosevelt's presidential airplane, but you can check it out, in addition to other military weapons and aircraft, online in the Air Force's official museum.
MoMA (The Museum of Modern Art): New York's extensive collection is available for view online.
Museum of Fine Arts, Boston: The 16 virtual exhibits include a special section on 21st Century Designer Fashion.

Zoos and Aquariums

The Cincinnati Zoo: Check in around 3 p.m., because that's the time the Zoo holds a daily Home Safari on its Facebook Live Feed.
Atlanta Zoo: The Georgia zoo keeps a "Panda Cam" livestream on its website.
Georgia Aquarium: Sea-dwellers like African penguins and Beluga Whales are the stars of this aquarium's live cam.
Houston Zoo: There are plenty of different animals you can check in on with this zoo's live cam, but we highly recommend watching the playful elephants.
The Shedd Aquarium: This Chicago aquarium shares some pretty adorable behind-the-scenes footage of their residents on Facebook.
San Diego Zoo: With what may be the most live cam options, this zoo lets you switch between koalas, polar bears, and tigers in one sitting.
Monterey Bay Aquarium: It can be Shark Week every week thanks to live online footage of Monterey Bay's Habitat exhibit.
National Aquarium: Walk through tropical waters to the icy tundra in this floor-by-floor tour of the famous, Baltimore-based aquarium.

Theme Parks

Walt Disney World: Set aside some time, because there's plenty to see here. Virtual tours you can take include Magic Kingdom, Animal Kingdom, and Epcot, just to name a few. There are also unofficial YouTube videos that feel just like you're on famous rides like the Frozen Ever After ride, It's a Small World, Monsters, Inc. Mike & Sulley to the Rescue!, and Pirates of the Caribbean.
LEGOLAND Florida Resort: The Great Lego Race and Miniland USA are just two of the attractions you can check out in a virtual tour of the park.
SeaWorld Orlando: The virtual tour of Seaworld includes a tour of Discovery Cove and the option to"ride" the steel roller coaster Mako

Thursday, April 9, 2020

LINK BETWEEN po_headers_all AND oe_order_headers_all ,link between sales order and purchase order in Oracle apps r12

--LINK BETWEEN po_headers_all AND oe_order_headers_all
--link BETWEEN po AND so IN oracle apps LINK BETWEEN po_headers_all AND oe_order_headers_all
OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID==PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID
OE_ORDER_LINES_ALL.ORIG_SYS_DOCUMENT_REF=PO_REQUISITION_HEADERS_ALL.SEGMENT1(REQUISITION number)

select OOH.ORDER_NUMBER SALES_ORDER,
PRH.SEGMENT1 REQ,
PH.SEGMENT1 'PO Number'
from OE_ORDER_HEADERS_ALL OOH,
PO_REQUISITION_HEADERS_ALL PRH,
PO_REQUISITION_LINES_ALL PRL,
PO_REQ_DISTRIBUTIONS_ALL RD,
PO_HEADERS_ALL PH,
PO_DISTRIBUTIONS_ALL D
where 1 = 1
and PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
and PRH.REQUISITION_HEADER_ID = OOH.SOURCE_DOCUMENT_ID
and ORDER_NUMBER = '344444' --Sales Order Number
and PRH.SEGMENT1 = 'WE23333' -- Requisition Number
and RD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
and D.REQ_DISTRIBUTION_ID = RD.DISTRIBUTION_ID
and PH.PO_HEADER_ID = D.PO_HEADER_ID link between PO REQUISITION and OE_ORDER_HEADERS_ALL
select OOH.ORDER_NUMBER SALES_ORDER,
PRH.SEGMENT1 REQUISATION
from OE_ORDER_HEADERS_ALL OOH,
PO_REQUISITION_HEADERS_ALL PRH,
PO_REQUISITION_LINES_ALL PRL
where 1 = 1
and PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
and PRH.REQUISITION_HEADER_ID = OOH.SOURCE_DOCUMENT_ID
and ORDER_NUMBER = '344444' --Sales Order Number
and PRH.SEGMENT1 = 'WE23333' -- Requisition Number
and PRH.TYPE_LOOKUP_CODE = 'INTERNAL'

--JOIN BETWEEN rcv_shipment_lines AND oe_order_lines_all
select OOH.ORDER_NUMBER SALES_ORDER,
PRH.SEGMENT1 REQUISATION,
PH.SEGMENT1 PO_NUMBER,
RSH.RECEIPT_NUM RECEIPT_NUM
from OE_ORDER_HEADERS_ALL OOH,
PO_REQUISITION_HEADERS_ALL PRH,
PO_REQUISITION_LINES_ALL PRL,
PO_REQ_DISTRIBUTIONS_ALL RD,
PO_HEADERS_ALL PH,
PO_DISTRIBUTIONS_ALL D,
RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH
where 1 = 1
and PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
and PRH.REQUISITION_HEADER_ID = OOH.SOURCE_DOCUMENT_ID
and ORDER_NUMBER = '344444' --Sales Order Number
and PRH.SEGMENT1 = 'WE23333' -- Requisition Number
and RD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
and D.REQ_DISTRIBUTION_ID = RD.DISTRIBUTION_ID
and PH.PO_HEADER_ID = D.PO_HEADER_ID
and PH.PO_HEADER_ID =RSL.PO_HEADER_ID
and RSL.SHIPMENT_HEADER_ID =RSH.SHIPMENT_HEADER_ID

Tuesday, April 7, 2020

Query to retrieve Transaction & Receipt details. r12

Query to retrieve Transaction & Receipt details.

SELECT rcta.trx_number transaction_number ,
haou1.name operating_unit ,
rctta.name transaction_type ,
rcta.trx_date transaction_date ,
hca.account_name bill_to_customer ,
hl.address1
||' ,'
||hl.city
||' ,'
||hl.state
||' ,'
||hl.postal_code
||' ,'
||hl.country bill_to_address,
gsob.name ledger ,
rcta.interface_header_attribute1 order_number ,
rcta.interface_header_attribute2 order_type ,
rcta.interface_header_context invoice_source ,
CASE rcta.complete_flag
WHEN 'Y'
THEN 'YES'
WHEN 'N'
THEN 'NO'
END transaction_complete ,
rtt.name payment_terms ,
haou.name ship_from_organization ,
rctla.line_number ,
rctla.line_type ,
rcta.ship_via ,
msib.segment1 item ,
rctla.description item_description ,
rctla.quantity_ordered ,
rctla.quantity_invoiced ,
rctla.quantity_credited ,
rctla.unit_standard_price item_standard_price ,
rctla.unit_selling_price item_selling_price ,
fct.name currency ,
(rctla.unit_selling_price * rctla.quantity_invoiced) line_total ,
(SELECT SUM(unit_selling_price * quantity_invoiced)
FROM ra_customer_trx_lines_all
WHERE rcta.customer_trx_id=customer_trx_id
) invoice_total,
NVL(acra.receipt_number,'No receipt created for this transaction') receipt_number,
(SELECT name
FROM ar_receipt_methods
WHERE acra.receipt_method_id=receipt_method_id
) receipt_method ,
(SELECT meaning
FROM fnd_lookup_values
WHERE acra.type =lookup_code
AND lookup_type ='CASH_RECEIPT_TYPE'
) receipt_type ,
acra.receipt_date ,
acra.amount received_amount ,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_code=araa.status
AND lookup_type ='PAYMENT_TYPE'
) receipt_status ,
acra.comments ,
hp1.party_name bank_name ,
hp.party_name branch_name ,
cba.bank_account_name ,
cba.bank_account_num
FROM ra_customer_trx_all rcta ,
hz_cust_accounts_all hca ,
hz_cust_acct_sites_all hcasa ,
hz_party_sites hps ,
hz_locations hl ,
ra_terms_tl rtt ,
gl_sets_of_books gsob ,
ra_customer_trx_lines_all rctla ,
ra_cust_trx_types_all rctta ,
fnd_currencies_tl fct ,
hr_all_organization_units haou ,
hr_all_organization_units haou1 ,
mtl_system_items_b msib ,
ar_receivable_applications_all araa ,
ar_cash_receipts_all acra ,
hz_parties hp1,
hz_parties hp ,
ce_bank_accounts cba
WHERE rcta.term_id =rtt.term_id --to get payment terms
AND rcta.invoice_currency_code =fct.currency_code--to get currency
AND fct.language ='US'
AND rcta.org_id =haou1.organization_id--to get operating unit
AND rcta.sold_to_customer_id =hca.cust_account_id --TO GET CUSTOMER DETAIL
AND hca.cust_account_id =hcasa.cust_account_id--to get account site
AND rcta.org_id =hcasa.org_id
AND hcasa.party_site_id =hps.party_site_id --to get account site
AND hps.location_id =hl.location_id --to get site location
AND rctla.inventory_item_id =msib.inventory_item_id(+)--to get item
AND rctla.org_id =msib.organization_id(+)
AND rcta.set_of_books_id =gsob.set_of_books_id --for set of books
AND to_number(rcta.interface_header_attribute10)=haou.organization_id(+)--ship from organization(warehouse)
AND rcta.cust_trx_type_id =rctta.cust_trx_type_id --transaction type
AND rcta.org_id =rctta.org_id
AND rcta.customer_trx_id =rctla.customer_trx_id --for line details
AND rcta.customer_trx_id =araa.applied_customer_trx_id(+)--for receipt application
AND rcta.trx_number = :transaction_number --'10037542'
AND araa.cash_receipt_id =acra.cash_receipt_id(+) --to get receipt details
AND acra.remit_bank_acct_use_id =cba.bank_account_id(+) --for beneficiary bank account
AND cba.bank_branch_id =hp.party_id(+) --for branch name
AND cba.bank_id =hp1.party_id(+); --for bank name

Monday, April 6, 2020

Query to Find Receipt Class and its GL Combination Query

Query to Find Receipt Class and its GL Combinition Query

SELECT ARC.NAME ReceiptClass,

ARC.CREATION_METHOD_CODE Creation_Mehthod,

DECODE(ARC.REMIT_METHOD_CODE,

'STANDARD',

'Standard',

NULL,

'No Remittance') Remittance_Method,

DECODE(ARC.CLEAR_FLAG,

'Y',

'By Matching',

NULL,

'Directly',

'Directly') Clearance_Method,

ARM.NAME Receipt_Mehtod_Name,

ARM.PRINTED_NAME Printed_Name,

ARM.START_DATE Effective_Date,

ARM.END_DATE End_Date,

HOU.NAME Operating_Unit,

CBB.bank_name Bank_Name,

CBB.bank_branch_name Branch_Name,

CBA.BANK_ACCOUNT_NAME Bank_Account_Name,

CBA.CURRENCY_CODE Currency,

RM.MIN_RECEIPT_AMOUNT Min_Receipt_Amount,

RM.RISK_ELIMINATION_DAYS Risk_Elimination_Days,

RM.CLEARING_DAYS Clearing_Days,

RM.OVERRIDE_REMIT_ACCOUNT_FLAG Override_Bank,

RM.START_DATE Effective_Days,

RM.END_DATE End_Days,

RM.PRIMARY_FLAG Primary_Flag,

(select gcc.concatenated_segments