data template in xml publisher

The Data Template Definition:

The data template is an XML document that consists of four basic sections:
1. Define Parameters
2. Define triggers
3. Define Data Query
4. Define Data Structure
This structure is shown in the following graphic:

To build XML report the following steps are involved.
Step 1: Designthe Data Template XML File.
Step 2:Create the Data Definition in XML Publisher and associate it with Data Template
Step 3:Define a Concurrent Program to generate the Data XML File.
Step 4: Define the RTF Template using the Generated Data XML.
Step 5: Registering the Template with XML Publisher
Step 6: Execute the concurrent program to generate the PDF report.

Step 2: Create the Data Definition in XML Publisher and associate it with Data Template.

Step 3: Define a Concurrent Program to generate the Data XML File.

The short name in the above concurrent program is the code what you give while defining data definition.

Then run the above created concurrent program and get the xml output.

Save the output.

Step 4: Define the RTF Template using the Generated Data XML.
Go to word document and design your rtf Template.
Step 5: Registering the Template with XML Publisher.

Step 6: Execute the concurrent program to generate the PDF report.

Ur's
AmarAlam

oracle order management backorder

Back Orders:

The Oracle "term" backorder is a "status" on the order line or delivery line indicating that you have tried to release an order for picking in your warehouse, but that the pick release was UNSUCCESSFUL because there was no available inventory.(Backorder can be partial or complete). The Oracle term backorder does NOT mean that you have open purchase orders for the out-of-stock item from your vendors.

The term backorder is also used in business a little differently than in Oracle. The term "An item is on backorder" usually means that the item is not in stock, but the shipping company has already placed purchase orders from their suppliers to restock the item.

Back Order is when you do not fulfill the Sales Order, or if the inventory is out of stock for delivery to customer.

Reasons for a Sales Order to get Backordered!

A Sales Order can get into backorder state after performing Pick Release on a Sales Order but there are many reasons for a Sales Order to get backorder. Today I will list the possible reasons for a Sales Order to get backorder.

1) Order on Hold
2) Inventory Period NOT open
3) No enough on-hand quantity
4) No enough quantity to reserve/transact
5) No on-hand quantity in required sub-inventory
6) The Lot from which items are selected is inactive/expired
7) Wrong Item reservation (even inventory have enough quantity)
8) Inventory reserved for other sales orders
9) Inventory picked-up by other sales orders
10) Previously done return to stock not properly performed
11) Move order is in pending state.

NOTE: This is not the end of the list, there will be many reasons for a sales order to get backorder. I will keep this list updated as and when I come across some more new reasons.


Ur's
AmarAlam

SCM QUERIES

To Find Duplicate Item Category Code

SELECT category_set_name, category_concat_segments, COUNT (*)
FROM mtl_category_set_valid_cats_v
WHERE (category_set_id = 1)
GROUP BY category_set_name, category_concat_segments
HAVING COUNT (*) > 1
ORDER BY category_concat_segments

Get Number Of canceled requisition

SELECT a.AUTHORIZATION_STATUS,(a.ORG_ID),(SELECT distinct hr.per_all_people_f.first_name|| ‘ ‘|| hr.per_all_people_f.middle_names|| ‘ ‘|| hr.per_all_people_f.last_name “Employee Name”
FROM hr.per_all_people_f
where hr.per_all_people_f.PERSON_ID in
(select employee_id from fnd_user fu where fu.user_id = a.CREATED_BY)) CREATED_BY,count(SEGMENT1 )
FROM
po_requisition_headers_all a
WHERE
a.creation_date BETWEEN TO_DATE(’01/01/2007′, ‘DD/MM/YYYY’)
and TO_DATE(’30/05/2007′, ‘DD/MM/YYYY’)
and a.AUTHORIZATION_STATUS = ‘CANCELLED’
group by a.AUTHORIZATION_STATUS,a.ORG_ID,a.CREATED_BY

Number of line processed in Order Management

SELECT COUNT (line_id) “Order Line Processed”
FROM oe_order_lines_all
WHERE creation_date BETWEEN TO_DATE (:Fdate, ‘DD/MM/YYYY’)
AND TO_DATE (:tdate, ‘DD/MM/YYYY’)
AND flow_status_code = ‘CLOSED’;

To Check Item Catogry For Inventory master (No Of Segments May Varray)

SELECT ood.organization_name,
segment1|| ‘-’|| segment2|| ‘-’|| segment3 catgory
FROM org_organization_definitions ood,
mtl_categories_vl mcv,
mtl_category_sets mcs
WHERE mcs.structure_id = mcv.structure_id
ORDER BY ood.organization_name

Check Locators for inventory Inventory Org Wise(Number of segment may varray)

SELECT mil.segment1 loc_seg1, mil.segment11 loc_seg11, mil.segment2 loc_seg2,
mil.segment3 loc_seg3, mil.segment4 loc_seg4, mil.segment5 loc_seg5,
mil.segment6 loc_seg6,ood.ORGANIZATION_NAME,mil.SUBINVENTORY_CODE
FROM mtl_item_locations mil,org_organization_definitions ood
where mil.ORGANIZATION_ID = ood.ORGANIZATION_ID

Display All Subinventories Setup

select msi.secondary_inventory_name, MSI.SECONDARY_INVENTORY_NAME “Subinventory”, MSI.DESCRIPTION “Description”,
MSI.DISABLE_DATE “Disable Date”, msi.PICKING_ORDER “Picking Order”,
gcc1.concatenated_segments “Material Account”,
gcc2.concatenated_segments “Material Overhead Account”,
gcc3.concatenated_segments “Resource Account”,
gcc4.concatenated_segments “Overhead Account”,
gcc5.concatenated_segments “Outside Processing Account”,
gcc6.concatenated_segments “Expense Account”,
gcc7.concatenated_segments “Encumbrance Account”,
msi.material_overhead_account,
msi.resource_account,
msi.overhead_account,
msi.outside_processing_account,
msi.expense_account,
msi.encumbrance_account
from mtl_secondary_inventories msi,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
gl_code_combinations_kfv gcc3,
gl_code_combinations_kfv gcc4,
gl_code_combinations_kfv gcc5,
gl_code_combinations_kfv gcc6,
gl_code_combinations_kfv gcc7
where msi.material_account = gcc1.CODE_COMBINATION_ID(+)
and msi.material_overhead_account = gcc2.CODE_COMBINATION_ID(+)
and msi.resource_account = gcc3.CODE_COMBINATION_ID(+)
and msi.overhead_account = gcc4.CODE_COMBINATION_ID(+)
and msi.outside_processing_account = gcc5.CODE_COMBINATION_ID(+)
and msi.expense_account = gcc6.CODE_COMBINATION_ID(+)
and msi.encumbrance_account = gcc7.CODE_COMBINATION_ID(+)
order by msi.secondary_inventory_name

To Select Unit Of measure exist in ebusiness suite

select uom_code,unit_of_measure
from mtl_units_of_measure

Query to find out Customer Master Information. Customer Name, Account Number, Adress etc.

select p.PARTY_NAME,ca.ACCOUNT_NUMBER,loc.address1,loc.address2,loc.address3,loc.city,loc.postal_code,
loc.country,ca.CUST_ACCOUNT_ID
from apps.ra_customer_trx_all I,
apps.hz_cust_accounts CA,
apps.hz_parties P,
apps.hz_locations Loc,
apps.hz_cust_site_uses_all CSU,
apps.hz_cust_acct_sites_all CAS,
apps.hz_party_sites PS
where I.COMPLETE_FLAG =’Y’
and I.bill_TO_CUSTOMER_ID= CA.CUST_ACCOUNT_ID
and ca.PARTY_ID=p.PARTY_ID
and I.bill_to_site_use_id=csu.site_use_id
and csu.CUST_ACCT_SITE_ID=cas.CUST_ACCT_SITE_ID
and cas.PARTY_SITE_ID=ps.party_site_id
and ps.location_id=loc.LOCATION_ID

Query to find on Hand Quantity

select sum(transaction_quantity) from MTL_ONHAND_QUANTITIES
where inventory_item_id=9
and organization_id=188

Qunatity on order, Expected Deliver

select sum(ordered_quantity),a.SCHEDULE_SHIP_DATE
from oe_order_lines_all a
where inventory_item_id=10
and ship_from_org_id=188
group by a.SCHEDULE_SHIP_DATE

Query to find Item Code, Item Description Oracle Item Master Query

select item, description from mtl_system_items_b
where inventory_item_id=&your_item
and organization_id=&organization_id) item

Query to Find out On Hand Quantity of specific Item Oracle inventory

select sum(transaction_quantity) from mtl_onhand_quantity_details
where inventory_item_id=&your_item
and organization_id=&organization_id

Qty On Order,Expected deivery date

select sum(ordered_quantity),scheduled_ship_date from oe_order_lines_all
where inventory_item_id=&your_item
and ship_from_org_id=&organization_id
group by scheduled_ship_date) order_info

–Total Received Qty
select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0)

Total received Qty in 9 months

select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0
and transaction_date between trunc(sysdate) and trunc(sysdate-270))

Total issued quantity in 9 months

select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0 and="" between="" consumption="" monthly="" p="" sysdate-270="" sysdate="" tot_iss_qty_9mths="" transaction_date="" trunc="" verage="">(select sum(transaction_quantity)/30 from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0 p="">
Display all categories that the Item Belongs

SELECTunique micv.CATEGORY_SET_NAME “Category Set”,
micv.CATEGORY_SET_ID “Category Set ID”,
decode( micv.CONTROL_LEVEL,
1, ‘Master’,
2, ‘Org’,
‘Other’) “Control Level”,
micv.CATEGORY_ID “Category ID”,
micv.CATEGORY_CONCAT_SEGS “Category”
FROM
MTL_ITEM_CATEGORIES_V micv

Another Query to Get Onhand Qty With Oranization ID, Item Code, Quantity
SELECT organization_id,
(SELECT ( msib.segment1|| ‘-’|| msib.segment2|| ‘-’|| msib.segment3|| ‘-’|| msib.segment4)
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = moq.organization_id) “Item Code”,
(SELECT description
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
moq.inventory_item_id
AND msib.organization_id = moq.organization_id)
“item Description”,
SUM (moq.transaction_quantity) onhandqty
FROM mtl_onhand_quantities moq
GROUP BY moq.organization_id, (moq.inventory_item_id)


Ur's
AmarAlam

Oracle Order Management Interview Questions

What is a Standard Item?
A standard item is a finished good that is not an assembled or configured item, a service or a component.
What happens when you enter an Order?
After you enter an order, the items on the order are validated in Oracle Inventory; a price is calculated for the items using the pricing engine; the availability of the items are checked and may be reserved; the items are then pick released and shipped to the customer.
Explain about Processing Constraints
Processing Constraints are a security framework where you can define rules in Oracle Order Management that validate back-end operations such as Create, Update, Delete and Cancel.
Examples:
You cannot change the Order Type in the order header once the order is Booked.
You cannot delete the line in an order once the Order Status is Closed (if the order is ship confirmed or invoice interfaced).
What are Defaulting Rules?
Defaulting Rules enable you to speed up data entry by passing values in the window based on some key values you have entered.
Examples:
In the order header, Ship-To Address and Bill-To Address values default in the window once you enter the customer name or number.
If you enter an item number, the Unit of Measure and Item Description automatically default on the order line.
How Drop Ship Order is created?
Drop shipments are created as sales orders in Order Management, indicated as drop shipments when their Source Type is entered as External.
When will you create a Drop Ship Order?
Drop shipments are used due to the following reasons:
·         Customer requires an item that is not normally stocked
·         Customer requires a large quantity of the item which is not available with you
·         It is more economical when the supplier ships directly to the customer.
What are the types of Drop Ship Order?
There are three types of drop shipments:
Full Drop Shipment:
The seller sends the purchase order to the supplier for the full quantity that the customer had ordered
Normal Shipments and Full Drop Shipment:
The seller ships some goods from inventory to the customer, and the other goods are always shipped from an external source (supplier)
Normal Shipments and Partial Drop Shipment:
If the seller has only part of the quantity available for shipping to the customer, then that quantity is shipped.
The user looks at the availability for the order qty by using the ATP check and if the whole qty is not available the balance qty is fulfilled using the drop ship process.
A purchase order is created for the remaining quantity which the seller was not able to fulfill.
What is a Sales Agreement?
A Sales Agreement is a supplier’s representation of an agreement with a customer for the supplier to sell and the customer to buy goods or services.
What is ASN?
ASN stands for Advanced Shipment Notice. ASN is transmitted via Electronic Data Interchange (EDI) from a supplier to let the receiving organization know that a shipment is coming. The ASN contains details including shipment date, time, and identification number, packing slip data, freight information, and item detail including cumulative received quantities, purchase order number, and returnable container information.
What are Modifiers and Qualifiers?
Modifiers are discount, surcharge or special charge that may be applied to the base price and may alter the value of the item. It can be applied either at order level or at the line level.
A qualifier helps you define who is eligible for a price list or modifier. A qualifier can be a customer name, a customer class, an order type, or an order amount that can span orders.
Explain One Step Pick Release Process:
The one-step process consists of selecting the Auto Allocate box on the Inventory tab and the Auto Pick Confirm box on the Inventory tab when you run Pick Release, which means that the Pick Recommendation is automatically created and Pick Confirmed without any manual intervention.
Explain Two Step Pick Release Process:
The two-step process consists of selecting Auto Allocate (not Auto Pick Confirm), which creates a move order that is automatically detailed.  It enables you to view the Pick Recommendation and provides the opportunity to change quantity, location, and subinventory. You can report a missing quantity at the Pick Confirmation step in the Transact Move Orders window. Once you have made your changes, you can transact the move order to Pick Confirm the inventory.
Explain Three Step Pick Release Process:
The three-step process consists of selecting neither the Auto Allocate or Auto Pick Confirm check boxes. This creates a move order whose details you can enter manually or automatically in the Transact Move Orders window. After the details are entered, you can transact the move order to pick confirm the transaction.
What are various ways you can run Pick Release?
Release Sales Orders Window
Using a Concurrent Program
SRS – Pick Selection List Generation – SRS.
Release Rule Name need to be specified to proceed with the pick release when using the concurrent program or SRS.
At which stage the item is moved from ware house to staging inventory?
During Pick Confirm
What happens during Ship Confirm?
Ship Confirm indicate that the items are loaded onto the carrier from staging location.
At which stage the Oracle Inventory is decremented and Sales Order Line status is updated?
During Ship Confirm
What is Autoinvoice?
AutoInvoice is a concurrent program in Oracle Receivables that performs invoice processing at both the order and line levels. Once an order or line or set of lines is eligible for invoicing, the Invoice Interface workflow activity interfaces the data to Receivables. Oracle Order Management inserts records into the following interface tables: RA_INTERFACE_LINES and RA_INTERFACE_SALES_CREDITS.
If the order or lines are On Hold, what happens during Invoice Interface?
The Invoice Interface workflow activity will complete with a status of On Hold and the order details will not be interfaced to Receivables.
What is Credit Check?
Credit Checking feature is the process by which orders are validated and released against your credit checking business rules.
What are the two Invoicing Rules?
Advance Invoice and Arrears Invoice


Ur's
AmarAlam

R12 Multi-Org Access Control - Preferences

Multi-Org Access Control Preferences - Description :

Multi-Org Preferences allows you to control the list of operating units to which you have access. For example, a system administrator may create a security profile that has ten operating units assigned to it and assign it to your responsibility. But, you may only deal with five of the operating units on a daily basis and do not want work space cluttered with extraneous operating units. You could set up Multi-Org preferences to restrict the list of operating units; you have complete control over this and can change it at anytime. In addition, you can specify a default operating unit.

Multi-Org Access Control Preferences - Benefits:

•Increase Efficiency
–Save key strokes with default operating unit
–Limit access to operating units you use most
•User Level Control
–Eliminate using System Administrators; you can control your own access
•Reduce cost
–Perform processes quicker

Multi-Org Access Control Preferences Setup:

Most products have added the Preferences user interface to their responsibility menus. You can select preferred operating units which represent a subset of operating units assigned to your responsibility’s security profile. You can also set a default operating unit.

Multi-Org Access Control Preferences – Setup – Add to SubMenu:

To enable and display Preferences in your menu
1.Request that your System Administrator to add the function FNDMOPREFS to your menu definition
•The System Administrator should use either the System Administrator or Application Developer responsibility, and select the Menu (Application) option
2.Select your product’s menu and add the function named User Preferences (FNDMOPREFS)

Multi-Org Access Control Preferences – Setup – Set Preferences:

Multi-Org Preferences page:
•The header displays:
–The user name that you are logged in as
–The responsibility name
–The Security Profile that you are currently assigned to
•The Default Operating Unit region is where you select a default OU.
•Preferred Operating Units is where you select the subset of operating units you want to work with.


Ur's
AmarAlam

Definitions of the Technical Terms

DFF: Descriptive Flex Field:

Descriptive Flex Fields are used to gather additional information about your business entities beyond the information required by oracle Applications. In other words “a mechanism that lets you creates new fields in screens that are delivered by Oracle but Hidden to store your business information”.

KFF: Key Flex Field:

Key Flex Fields are used to define your own structure for many of the identifiers required by Oracle Applications and drive reporting i.e. General Ledger (Accounting Flexfield), Assets (Asset key Flexfield, Location Flexfield, Category Flexfield), Inventory (Item Category, system Items, Sales Order, item Catalogs), HR (People Group Flexfield, Job Code Flexfield) etc…

Value Sets:

Is a set of values or list of values attached with fields to restrict user to enter free text.

Legal Entity:

The business units where fiscal or tax reports are prepared. In other words A legal entity represents the designated legal employer for all employment-related activities. The legal authorities in a country recognize this organization as a separate employer.

Operating Unit:

The level at which Enterprise Resource Planning (ERP) transaction data is secured.

Organization:

An Organization may be a physical site or it can represent a collection of sites sharing certain characteristics.

Multi Org:

Multi-Org is a server-side (applications and database) enhancement that enables multiple business units in an enterprise to use a single installation of Oracle Applications products while keeping transaction data separate and secure.

Invoice:

An invoice or bill is a commercial document issued by a seller to the buyer, indicating the products, quantities, and agreed prices for products or services the seller has provided the buyer. An invoice indicates the buyer must pay the seller, according to the payment terms.

Receipt:

A receipt is a written acknowledgement that a specified article or sum of money has been received as an exchange for goods or services. The receipt acts as the title to the property obtained in the exchange.

Customer:

A customer is the buyer or user of the paid products of an individual or organization.

Vendor:

A vendor, or a supplier, is a supply chain management term meaning anyone who provides goods or services to a company. A vendor often manufactures inventoriable items, and sells those items to a customer.

Buyer:

In Apps the buyer is the person who creates documents like Purchase Order, Request for Quotations etc. buyer is the entity that decides to obtain the product. A buyer’s primary responsibility is obtaining the highest quality goods at the lowest cost. This usually requires research, writing requests for bids, proposals or quotes, and evaluation information received.

Purchase Order

A purchase order (PO) is a commercial document issued by a buyer to a seller, indicating types, quantities, and agreed prices for products or services the seller will provide to the buyer. Sending a PO to a supplier constitutes a legal offer to buy products or services. Acceptance of a PO by a seller usually forms a one-off contract between the buyer and seller, so no contract exists until the PO is accepted

Requisition:

A requisition is an Electronic or Printed formal written request for something,

Sales Order:

The sales order, sometimes abbreviated as SO, is an order received by a business from a customer. A sales order may be for products and/or services.

Packing Slip:

Shipping document that accompanies delivery packages, usually inside an attached shipping pouch or inside the package itself

Back Order:

A distribution term that refers to the status of items on a purchase order in the event that some or all of the inventory required to fulfill the order is insufficient to satisfy demand. This differs from a forward order where stock is available but delivery is postponed for another reason.


Ur's
AmarAlam

How to Find Concurrent Program Responsibility

select p.user_concurrent_program_name,
         rt.responsibility_name responsibility
    from FND_REQUEST_GROUPS g,
         FND_REQUEST_GROUP_UNITS u,
         FND_APPLICATION_TL a,
         FND_APPLICATION_TL a1,
         FND_CONCURRENT_PROGRAMS_TL p,
         FND_CONCURRENT_PROGRAMS p1,
         FND_RESPONSIBILITY r,
         FND_RESPONSIBILITY_TL rt
   where u.application_id = g.application_id
     and u.request_group_id = g.request_group_id
     and (u.request_unit_id = p.concurrent_program_id or u.request_unit_type = 'A')
     and u.unit_application_id = p.application_id
     and p.application_id = a.application_id
     and p.concurrent_program_id = p1.concurrent_program_id
     and g.application_id = a1.application_id
     and r.request_group_id = g.request_group_id
     and r.responsibility_id = rt.responsibility_id
     and rt.language = 'US'
     and p.language = 'US'
     and a.language = 'US'
     and a1.language = 'US'
     and p.user_concurrent_program_name like '%%'
   order by 1;


Ur's
AmarAlam

Query to find Supplier, Sites and Contact Information - R12

Here i am posting Query to find Supplier, Sites and Contact Information.

AP_SUPPLIERS                  
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS

In R12, Contact person information is stored in HZ_PARTIES table not in ap_supplier_contacts.

SELECT asp.vendor_id ,
asp.segment1 "Supplier Num" ,
asp.vendor_name "Supplier Name" ,
ass.vendor_site_code "site name" ,
hou.name "Operating Unit Name" ,
ass.address_line1 ,
ass.city ,
ass.state ,
ass.zip ,
ass.country ,
ass.phone ,
person.person_first_name ,
person.person_last_name ,
pty_rel.primary_phone_number ,
pty_rel.email_address
FROM ap_suppliers asp ,
ap_supplier_sites_all ass ,
ap_supplier_contacts apsc ,
hz_parties person ,
hz_parties pty_rel,
hr_operating_units hou
WHERE ass.vendor_id = asp.vendor_id
AND apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND ass.org_id = hou.organization_id
AND apsc.org_party_site_id = ass.party_site_id
AND asp.vendor_name = 'DELL Computers';


Ur's
AmarAlam

API to Update a Customer Site Use TCA R12 (hz_cust_account_site_v2pub.update_cust_site_use)

CREATE OR REPLACE procedure APPS.xxaj_site_use_status
is

                    x_return_status     varchar2(10);          
                    x_msg_count         number(10);              
                    x_msg_data          varchar2(1200);
                    p_object_version_number number(10):=4;
                    P_CUST_SITE_USE_REC hz_cust_account_site_v2pub.CUST_SITE_USE_REC_TYPE;

begin
       --Apps Initialization
       
         FND_GLOBAL.APPS_INITIALIZE (
                                            USER_ID => 1318,
                                            RESP_ID => 50583,
                                            RESP_APPL_ID => 401  
                                            );
                 
         P_CUST_SITE_USE_REC.site_use_id:=4126;
         P_CUST_SITE_USE_REC.status:= 'A';
         P_CUST_SITE_USE_REC.cust_acct_site_id :=3995;
         P_CUST_SITE_USE_REC.SITE_USE_CODE := 'BILL_TO';
         P_CUST_SITE_USE_REC.CREATED_BY_MODULE := 'TCA_V2_API';                    
                                           
         hz_cust_account_site_v2pub.update_cust_site_use
         (
                   p_init_msg_list             =>    'T',        
                   P_CUST_SITE_USE_REC        => P_CUST_SITE_USE_REC,
                   p_object_version_number     => p_object_version_number,  
                   x_return_status             => x_return_status,    
                   x_msg_count                 => x_msg_count ,  
                   x_msg_data                  => x_msg_data  
         );
            IF x_return_status = 'S' THEN
                dbms_output.put_line(' Now site use is active' );  
            ELSE
                IF NVL (x_msg_count, 0) > 1 THEN
                    FOR i IN 1 .. x_msg_count LOOP
             
                        dbms_output.put_line(' Error Status ' ||x_return_status);
                        dbms_output.put_line(' Error message ' ||x_msg_data);
                    END LOOP;
                ELSE
                    dbms_output.put_line(' Error message ' ||x_msg_data);
                END IF;
            END IF;
         commit;                              
exception when others then
    dbms_output.put_line(' Error Here'||sqlcode||sqlerrm);
end;
/

SQL> EXEC xxaj_site_use_status;


Ur's
AmarAlam

FND LOAD Scripts in Oracle

Forms Personalizations: 
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct ((NAME_OF_LDT)).ldt FND_FORM_CUSTOM_RULES function_name="OZF_FNDLVMLU"
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct ((NAME_OF_LDT)).ldt


Form Function:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ((NAME_OF_LDT)).ldt FUNCTION FUNCTION_NAME='((func name))'
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct ((NAME_OF_LDT)).ldt


Lookups :
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct ((NAME_OF_LDT)).ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ="XXDIS" LOOKUP_TYPE="((lkp name))"
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct ((NAME_OF_LDT)).ldt


Profile:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD @FND:patch/115/import/afscprof.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct ((NAME_OF_LDT)).ldt PROFILE PROFILE_NAME="((NAME_OF_profile))" APPLICATION_SHORT_NAME="CANON"


Concurrent Program: 
--------------------------------------
FNDLOAD (usrname)/(paswrd) O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ((NAME_OF_LDT)).ldt PROGRAM APPLICATION_SHORT_NAME="XXDIS" CONCURRENT_PROGRAM_NAME="((prg_name))"


Request Group:
--------------------------------------
FNDLOAD (usrname)/(paswrd) O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct ((NAME_OF_LDT)).ldt REQUEST_GROUP REQUEST_GROUP_NAME="System Administrator Reports" APPLICATION_SHORT_NAME="FND"

Flexi field:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_LINES'

Flexi field ....category/CONTEXT:
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt DESC_FLEX APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="FND_COMMON_LOOKUPS" DFF_CONTEXT DESCRIPTIVE_FLEX_CONTEXT_CODE="((FLEX_CONTEXT))"


Valuset :
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt VALUE_SET FLEX_VALUE_SET_NAME="CANON_E379_MERCH_DROPSHIP_WH_VS"
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt


Alert :
-------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct ((NAME_OF_LDT)).ldt ALR_ALERTS APPLICATION_SHORT_NAME='WSH'
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct ((NAME_OF_LDT)).ldt


Ur's
AmarAlam

Global Temporary Tables

Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

1) Creation of Global Temporary Tables
2) Miscellaneous Features

Creation of Global Temporary Tables:

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT PRESERVE ROWS;

Miscellaneous Features:

1) If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
2) Data in temporary tables is stored in temp segments in the temp table space.
3) Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
4) Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
5) Views can be created against temporary tables and combinations of temporary and permanent tables.
6) Temporary tables can have triggers associated with them.
7) Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
8) Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
9) There are a number of restrictions related to temporary tables but these are version specific.


Ur's
AmarAlam

Oracle Applications Shortcut Keys

Function	Hot Key
Clear Field	F5
Clear Form	F8
Clear Record	F6
Clear Block	F7
Commit / Save	Ctrl-S
Delete Record	Ctrl-↑
Down	↓
Duplicate Field	Shift-F5
Duplicate Record	Shift-F6
Edit	Ctrl-E
Enter Query	F11
Execute Query	Ctrl-F11
Exit	F4
Insert Record	Ctrl-↓
List of Values	Ctrl-L
Next Block	Shift-PgDn
Next Record	↓
Previous Block	Shift-PgUp
Previous Field	Shift-Tab
Previous Record	↑
Next Field	Tab
Print	Ctrl-P
Scroll Down	PgDn
Scroll Up	PgUp
Show Keys	Ctrl-K
Up	↑

Query to list Customer (Party), Account, Site data

As one of client was going through its customer related data clean up in its Oracle R12 applications, I wrote the following query to retrieve all the information that were needed to provide a list of its customer (party), account, site, address, collector, and other related information.

--=============================================================================

-- Filename   : Customer Data Query

-- Programmer : Abul Mohsin

-- Date       : 29-Oct-2012

-- Language   : SQL

-- Module     : AR

-- Purpose    : Lists Customer (Party), Customer Account, and Customer Site

--              related information.

--=============================================================================

SELECT

       ----------------------------------------------------------

       -- Party Information

       ----------------------------------------------------------

       hp.party_number                      "Registry ID",

       hp.party_name                        "Party Name",

       hp.party_type                        "Party Type",

       DECODE(hp.status,

              'A', 'Active',

              'I', 'Inactive',

              hp.status)                    "Party Status",

       ----------------------------------------------------------

       -- Account Information

       ----------------------------------------------------------

       hca.account_number                   "Account Number",

       DECODE(hca.status,

              'A', 'Active',

              'I', 'Inactive',

              hca.status)                   "Account Status",

       hca.account_name                     "Account Description",

       hca.customer_class_code              "Classification",

       DECODE(hca.customer_type,

              'R', 'External',

              'I', 'Internal',

              hca.customer_type)            "Account Type",

       ----------------------------------------------------------

       -- Site Information

       ----------------------------------------------------------

       hps.party_site_number                "Customer Site Number",

       DECODE(hcas.status,

              'A', 'Active',

              'Inactive')                   "Site Status",

       DECODE(hcas.bill_to_flag,

              'P', 'Primary',

              'Y', 'Yes',

              hcas.bill_to_flag)            "Bill To Flag",

       DECODE(hcas.ship_to_flag,

              'P', 'Primary',

              'Y', 'Yes',

              hcas.ship_to_flag)            "Ship To Flag",

       hcas.cust_acct_site_id               "Customer Acct Site ID",

       ----------------------------------------------------------

       -- Address Information

       ----------------------------------------------------------

       hl.address1                          "Address1",

       hl.address2                          "Address2",

       hl.address3                          "Address3",

       hl.address4                          "Address4",

       hl.city                              "City",

       hl.state                             "State",

       hl.postal_code                       "Zip Code",

       ter.name                             "Territory",

       ----------------------------------------------------------

       -- DFF Information (specific to client)

       ----------------------------------------------------------

       hcas.attribute4                      "SMG Key",

       hcas.attribute8                      "GLN Key",

       hca.attribute3                       "Credit Approval Date",

       hca.attribute7                       "Credit Approved By",

       hca.attribute4                       "Acct Opened Date",

       hca.attribute5                       "Credit Collection Status",

       hca.attribute1                       "BPCS Last Trx Date",

       hca.attribute2                       "BPCS Avg Pay Days",

       hca.attribute6                       "BPCS RCM Reference",

       ----------------------------------------------------------

       -- Collector Information

       ----------------------------------------------------------

       col.name                             "Collector Name",

       ----------------------------------------------------------

       -- Account Profile Information

       ----------------------------------------------------------

       hcp.credit_checking                  "Credit Check Flag",

       hcp.credit_hold                      "Credit Hold Flag",

       hcpa.auto_rec_min_receipt_amount     "Min Receipt Amount",

       hcpa.overall_credit_limit            "Credit Limit",

       hcpa.trx_credit_limit                "Order Credit Limit",

       ----------------------------------------------------------

       -- Attachment Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM fnd_documents_vl doc,

                   fnd_lobs         blo,

                   fnd_attached_documents att

             WHERE doc.media_id = blo.file_id

               AND doc.document_id = att.document_id

               AND att.entity_name = 'AR_CUSTOMERS'

               AND att.pk1_value   = hca.cust_account_id

               AND ROWNUM = 1), 'N'

       ) "Attachment Flag",

       ----------------------------------------------------------

       -- Party Relationship Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM hz_cust_acct_relate_all hzcar

             WHERE hzcar.cust_account_id = hca.cust_account_id

               AND hzcar.relationship_type = 'ALL'

               AND ROWNUM = 1), 'N'

       ) "Party Relationship Flag",

       ----------------------------------------------------------

       -- Account Relationship Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM hz_cust_acct_relate_all hzcar

             WHERE hzcar.cust_account_id = hca.cust_account_id

               AND ROWNUM = 1), 'N'

       ) "Account Relationship Flag",

       ----------------------------------------------------------

       -- Party Contact Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM hz_parties hp2

             WHERE 1=1

               AND hp2.party_id = hp.party_id

               AND (

                    hp2.url IS NOT NULL OR

                    -- LENGTH(TRIM(hp.email_address)) > 5

                    INSTR(hp2.email_address, '@') > 0 OR

                    hp2.primary_phone_purpose IS NOT NULL

                    )

           ), 'N'

       ) "Party Contact Flag",

       ----------------------------------------------------------

       -- Account Contact Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM hz_contact_points

             WHERE status = 'A'

               AND owner_table_id =

                   (SELECT hcar.party_id

                      FROM hz_cust_account_roles   hcar,

                           ar_contacts_v           acv

                     WHERE hcar.cust_account_id   = hca.cust_account_id

                       AND hcar.cust_account_role_id = acv.contact_id

                       AND hcar.cust_acct_site_id IS NULL  -- look for account level only

                       AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)

                    )

               AND ROWNUM = 1), 'N'

       ) "Account Contact Flag",

       ----------------------------------------------------------

       -- Site Contact Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM hz_contact_points

             WHERE status = 'A'

               AND owner_table_id =

                   (

                      SELECT hcar.party_id

                        FROM hz_cust_account_roles   hcar,

                             ar_contacts_v           acv

                       WHERE hcar.cust_acct_site_id     =  hcas.cust_acct_site_id

                         AND hcar.cust_account_role_id  =  acv.contact_id

                         AND ROWNUM = 1  -- add this row to show inactive sites (i.e. with no site id)

                   )

               AND ROWNUM = 1), 'N'      -- any contact (email, phone, fax) would suffice this condition

       ) "Site Contact Flag"

  FROM

       hz_parties              hp,

       hz_party_sites          hps,

       hz_cust_accounts_all    hca,

       hz_cust_acct_sites_all  hcas,

       hz_customer_profiles    hcp,

       hz_cust_profile_amts    hcpa,

       hz_locations            hl,

       ra_territories          ter,

       ar_collectors           col

 WHERE

       1=1

   AND hp.party_id            =  hca.party_id

   AND hca.cust_account_id    =  hcas.cust_account_id(+)

   AND hps.party_site_id(+)   =  hcas.party_site_id

   AND hp.party_id            =  hcp.party_id  

   AND hca.cust_account_id    =  hcp.cust_account_id

   AND hps.location_id        =  hl.location_id(+)

   AND col.collector_id       =  hcp.collector_id

   AND hcas.territory_id      =  ter.territory_id(+)

   AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id

   ----

   AND hp.party_type          = 'ORGANIZATION'    -- only ORGANIZATION Party types

   AND hp.status              = 'A'               -- only Active Parties/Customers

   ----

   -- following conditions are for testing purpose only

   -- comment/uncomment as needed

   ----

   -- AND hp.party_number        = 11530

   -- AND hca.account_number     = 32253 --32396 --31753 --32253 --31038

 ORDER BY TO_NUMBER(hp.party_number), hp.party_name, hca.account_number;

Ur's

AmarAlam