The code below creates a sales order in entered status with one line record. Also the price adjustment is done for the line.
DECLARE
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
/*****************PARAMETERS****************************************************/
l_debug_level NUMBER := 1; -- OM DEBUG LEVEL (MAX 5)
l_org NUMBER := 5283; -- OPERATING UNIT
l_no_orders NUMBER := 1; -- NO OF ORDERS
l_user NUMBER := 28573; -- USER
l_resp NUMBER := 53073; -- RESPONSIBLILTY
l_appl NUMBER := 660; -- ORDER MANAGEMENT
/*****************INPUT VARIABLES FOR PROCESS_ORDER API*************************/
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.request_tbl_type;
l_line_adj_tbl oe_order_pub.line_adj_tbl_type;
/*****************OUT VARIABLES FOR PROCESS_ORDER API***************************/
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2 (2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2 (200);
b_return_status VARCHAR2 (200);
b_msg_count NUMBER;
b_msg_data VARCHAR2 (2000);
BEGIN
DBMS_APPLICATION_INFO.set_client_info (l_org);
MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org);
mo_global.init('ONT');
/*****************INITIALIZE DEBUG INFO*************************************/
IF (l_debug_level > 0)
THEN
l_debug_file := oe_debug_pub.set_debug_mode ('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel (l_debug_level);
oe_msg_pub.initialize;
END IF;
/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize (l_user, l_resp, l_appl);-- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.g_miss_header_rec;
/*****************POPULATE REQUIRED ATTRIBUTES **********************************/
l_header_rec.operation := oe_globals.g_opr_create;
l_header_rec.order_type_id := 2159; -- domestic return
l_header_rec.sold_to_org_id := 659018;
l_header_rec.ship_to_org_id := 635775;
l_header_rec.invoice_to_org_id := 635776;
l_header_rec.order_source_id := 9;
l_header_rec.booked_flag := 'N';
l_header_rec.price_list_id := 39825;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := 'USD';
l_header_rec.flow_status_code := 'ENTERED';
l_header_rec.cust_po_number := '1211314AFA';
/*****************INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
/*****************INITIALIZE LINE RECORD********************************/
l_line_tbl (1) := oe_order_pub.g_miss_line_rec;
l_line_tbl (1).operation := oe_globals.g_opr_create;
l_line_tbl (1).inventory_item_id := 826543;
l_line_tbl (1).ordered_quantity := 1;
--l_line_tbl(1).unit_selling_price := 2000; -- The price is done using adjustments
--l_line_tbl(1).unit_list_price := 2000;
l_line_tbl (1).calculate_price_flag := 'Y';
l_line_tbl (1).return_reason_code := 'B2';
--l_line_tbl(1).line_number := 1;
l_line_adj_tbl (1) := oe_order_pub.g_miss_line_adj_rec;
l_line_adj_tbl (1).operation := oe_globals.g_opr_create;
l_line_adj_tbl (1).list_header_id := 148129;
l_line_adj_tbl (1).list_line_id := 651550;
l_line_adj_tbl (1).change_reason_code := 'MANUAL';
l_line_adj_tbl (1).change_reason_text := 'Manually applied adjustments';
l_line_adj_tbl (1).operand := 2000;
l_line_adj_tbl (1).pricing_phase_id := 2;
l_line_adj_tbl (1).updated_flag := 'Y';
l_line_adj_tbl (1).applied_flag := 'Y';
l_line_adj_tbl (1).line_index := 1;
FOR i IN 1 .. l_no_orders
LOOP
/*****************CALLTO PROCESS ORDER API*********************************/
oe_order_pub.process_order (
p_api_version_number => l_api_version_number
, p_header_rec => l_header_rec
, p_line_tbl => l_line_tbl
, p_action_request_tbl => l_action_request_tbl
, p_line_adj_tbl => l_line_adj_tbl
-- OUT variables
, x_header_rec => l_header_rec_out
, x_header_val_rec => l_header_val_rec_out
, x_header_adj_tbl => l_header_adj_tbl_out
, x_header_adj_val_tbl => l_header_adj_val_tbl_out
, x_header_price_att_tbl => l_header_price_att_tbl_out
, x_header_adj_att_tbl => l_header_adj_att_tbl_out
, x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out
, x_header_scredit_tbl => l_header_scredit_tbl_out
, x_header_scredit_val_tbl => l_header_scredit_val_tbl_out
, x_line_tbl => l_line_tbl_out
, x_line_val_tbl => l_line_val_tbl_out
, x_line_adj_tbl => l_line_adj_tbl_out
, x_line_adj_val_tbl => l_line_adj_val_tbl_out
, x_line_price_att_tbl => l_line_price_att_tbl_out
, x_line_adj_att_tbl => l_line_adj_att_tbl_out
, x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out
, x_line_scredit_tbl => l_line_scredit_tbl_out
, x_line_scredit_val_tbl => l_line_scredit_val_tbl_out
, x_lot_serial_tbl => l_lot_serial_tbl_out
, x_lot_serial_val_tbl => l_lot_serial_val_tbl_out
, x_action_request_tbl => l_action_request_tbl_out
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
/*****************CHECK RETURN STATUS***********************************/
IF l_return_status = fnd_api.g_ret_sts_success
THEN
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('success');
END IF;
COMMIT;
ELSE
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('failure');
END IF;
ROLLBACK;
END IF;
END LOOP; -- END LOOP
/*****************DISPLAY RETURN STATUS FLAGS******************************/
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('process ORDER ret status IS: '
|| l_return_status);
DBMS_OUTPUT.put_line ('process ORDER msg data IS: '
|| l_msg_data);
DBMS_OUTPUT.put_line ('process ORDER msg COUNT IS: '
|| l_msg_count);
DBMS_OUTPUT.put_line ('header.order_number IS: '
|| TO_CHAR (l_header_rec_out.order_number));
DBMS_OUTPUT.put_line ('adjustment.return_status IS: '
|| l_line_adj_tbl_out (1).return_status);
DBMS_OUTPUT.put_line ('header.header_id IS: '
|| l_header_rec_out.header_id);
DBMS_OUTPUT.put_line ('line.unit_selling_price IS: '
|| l_line_tbl_out (1).unit_selling_price);
END IF;
/*****************DISPLAY ERROR MSGS*************************************/
IF (l_debug_level > 0)
THEN
FOR i IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (p_msg_index => i, p_encoded => fnd_api.g_false, p_data => l_data, p_msg_index_out => l_msg_index);
DBMS_OUTPUT.put_line ('message is: ' || l_data);
DBMS_OUTPUT.put_line ('message index is: ' || l_msg_index);
END LOOP;
END IF;
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('Debug = ' || oe_debug_pub.g_debug);
DBMS_OUTPUT.put_line ('Debug Level = ' || TO_CHAR (oe_debug_pub.g_debug_level));
DBMS_OUTPUT.put_line ('Debug File = ' || oe_debug_pub.g_dir || '/' || oe_debug_pub.g_file);
DBMS_OUTPUT.put_line ('****************************************************');
oe_debug_pub.debug_off;
END IF;
END;
/
Tuesday, September 28, 2010
Relation between AR invoice and Sales Order
SELECT ooha.order_number , oola.line_number so_line_number , oola.ordered_item , oola.ordered_quantity * oola.unit_selling_price so_extended_price , rcta.trx_number invoice_number , rcta.trx_date , rctla.line_number inv_line_number , rctla.unit_selling_price inv_unit_selling_price FROM oe_order_headers_all ooha , oe_order_lines_all oola , ra_customer_trx_all rcta , ra_customer_trx_lines_all rctla WHERE ooha.header_id = oola.header_id AND rcta.customer_trx_id = rctla.customer_trx_id AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id) AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)AND order_number = :p_order_number
Query for find PO details
set lines 150
set pages 150
execute fnd_client_info.set_org_context('1');
SELECT
decode(por.release_num,NULL, poh.segment1, poh.segment1 ||'-'|| por.release_num) PO_Number
, pol.line_num Line,
msi.segment1 Item_number,
msi.organization_id
, pov.vendor_name Vendor
, pol.item_revision Rev
, pol.item_description Description
, pll.shipment_num
, pod.distribution_num Distribution
, decode(plt.order_type_lookup_code, 'AMOUNT',NULL,pll.price_override) Unit_Price
, pll.promised_date Promised_Date
, pol.unit_meas_lookup_code Unit
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered,
'FIXED PRICE', POD.amount_ordered,
POD.quantity_ordered) Quantity_Amount_Ordered
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_billed,
'FIXED PRICE', POD.amount_billed,
POD.quantity_billed) Quantity_Amount_Billed
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_delivered,
'FIXED PRICE', POD.amount_delivered,
POD.quantity_delivered) Qty_Amount_Delivered
, DECODE (POL.order_type_lookup_code,
'RATE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
'FIXED PRICE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
(NVL(POD.quantity_ordered, 0) - NVL(POD.quantity_billed, 0)) /
DECODE (NVL(POD.quantity_ordered, 0), 0, 1, POD.quantity_ordered)) * 100 Percent_Unbilled
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
'FIXED PRICE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
(POD.quantity_ordered - NVL(POD.quantity_cancelled, 0)- NVL(POD.quantity_billed, 0)) * PLL.price_override) C_AMOUNT_OPEN_INV
, poh.po_header_id
, pol.po_line_id
, por.release_num
, poh.currency_code C_CURRENCY
, nvl(por.po_release_id,-1) release_id
FROM po_distributions_all pod
, mtl_system_items_b msi
, po_line_locations_all pll
, po_lines_all pol
, po_releases_all por
, po_headers_all poh
, po_vendors pov
, financials_system_parameters fsp
, po_line_types plt
WHERE poh.segment1='602088'
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pol.item_id = msi.inventory_item_id (+)
AND msi.organization_id = fsp.inventory_organization_id
AND poh.vendor_id = pov.vendor_id (+)
AND pll.po_release_id = por.po_release_id (+)
AND pol.line_type_id = plt.line_type_id
AND pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED')
AND nvl(pol.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(pll.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(por.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(por.cancel_flag,'N') = 'N'
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pll.cancel_flag,'N') = 'N'
ORDER BY pll.line_location_id
/
set pages 150
execute fnd_client_info.set_org_context('1');
SELECT
decode(por.release_num,NULL, poh.segment1, poh.segment1 ||'-'|| por.release_num) PO_Number
, pol.line_num Line,
msi.segment1 Item_number,
msi.organization_id
, pov.vendor_name Vendor
, pol.item_revision Rev
, pol.item_description Description
, pll.shipment_num
, pod.distribution_num Distribution
, decode(plt.order_type_lookup_code, 'AMOUNT',NULL,pll.price_override) Unit_Price
, pll.promised_date Promised_Date
, pol.unit_meas_lookup_code Unit
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered,
'FIXED PRICE', POD.amount_ordered,
POD.quantity_ordered) Quantity_Amount_Ordered
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_billed,
'FIXED PRICE', POD.amount_billed,
POD.quantity_billed) Quantity_Amount_Billed
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_delivered,
'FIXED PRICE', POD.amount_delivered,
POD.quantity_delivered) Qty_Amount_Delivered
, DECODE (POL.order_type_lookup_code,
'RATE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
'FIXED PRICE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
(NVL(POD.quantity_ordered, 0) - NVL(POD.quantity_billed, 0)) /
DECODE (NVL(POD.quantity_ordered, 0), 0, 1, POD.quantity_ordered)) * 100 Percent_Unbilled
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
'FIXED PRICE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
(POD.quantity_ordered - NVL(POD.quantity_cancelled, 0)- NVL(POD.quantity_billed, 0)) * PLL.price_override) C_AMOUNT_OPEN_INV
, poh.po_header_id
, pol.po_line_id
, por.release_num
, poh.currency_code C_CURRENCY
, nvl(por.po_release_id,-1) release_id
FROM po_distributions_all pod
, mtl_system_items_b msi
, po_line_locations_all pll
, po_lines_all pol
, po_releases_all por
, po_headers_all poh
, po_vendors pov
, financials_system_parameters fsp
, po_line_types plt
WHERE poh.segment1='602088'
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pol.item_id = msi.inventory_item_id (+)
AND msi.organization_id = fsp.inventory_organization_id
AND poh.vendor_id = pov.vendor_id (+)
AND pll.po_release_id = por.po_release_id (+)
AND pol.line_type_id = plt.line_type_id
AND pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED')
AND nvl(pol.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(pll.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(por.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(por.cancel_flag,'N') = 'N'
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pll.cancel_flag,'N') = 'N'
ORDER BY pll.line_location_id
/
AR invoices and Sales Orders
SELECT ooha.order_number,
oola.line_number so_line_number,
oola.ordered_item,
oola.ordered_quantity * oola.unit_selling_price so_extended_price,
rcta.trx_number invoice_number,
rcta.trx_date,
rctla.line_number inv_line_number,
rctla.unit_selling_price inv_unit_selling_price
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla
WHERE ooha.header_id = oola.header_id
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND order_number = :p_order_number
oola.line_number so_line_number,
oola.ordered_item,
oola.ordered_quantity * oola.unit_selling_price so_extended_price,
rcta.trx_number invoice_number,
rcta.trx_date,
rctla.line_number inv_line_number,
rctla.unit_selling_price inv_unit_selling_price
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla
WHERE ooha.header_id = oola.header_id
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND order_number = :p_order_number
Register TABLE in Oracle APPS
CREATE SYNONYM XXCUS.XX_LINES FOR APPS.XX_LINES;
begin
AD_DD.REGISTER_TABLE('XXCUS','XX_LINES','T');
end;
BEGIN
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','LINE_NO',1,'NUMBER',10,'Y','N');
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','ORDER_NO',2,'NUMBER',10,'Y','N');
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','ITEM',3,'VARCHAR2',20,'Y','N');
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','QTY',4,'NUMBER',10,'Y','N');
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','PRICE',5,'NUMBER',10,'Y','N');
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','AMOUNT',6,'NUMBER',10,'Y','N');
END;
Begin
ad_dd.register_primary_key('XXCUS','LINES_FK','XX_LINES','number','S','Y','Y');
end;
BEGIN
ad_dd.register_primary_key_column('XXCUS','LINES_FK','XX_LINES','ORDER_NO',1);
END;
begin
AD_DD.REGISTER_TABLE('XXCUS','XX_LINES','T');
end;
BEGIN
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','LINE_NO',1,'NUMBER',10,'Y','N');
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','ORDER_NO',2,'NUMBER',10,'Y','N');
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','ITEM',3,'VARCHAR2',20,'Y','N');
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','QTY',4,'NUMBER',10,'Y','N');
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','PRICE',5,'NUMBER',10,'Y','N');
AD_DD.REGISTER_COLUMN('XXCUS','XX_LINES','AMOUNT',6,'NUMBER',10,'Y','N');
END;
Begin
ad_dd.register_primary_key('XXCUS','LINES_FK','XX_LINES','number','S','Y','Y');
end;
BEGIN
ad_dd.register_primary_key_column('XXCUS','LINES_FK','XX_LINES','ORDER_NO',1);
END;
How to make a Oracle form Query only
Follow the following steps inorder to make a form as Query only/Read Only.
1. Login into the System Administrator/Application Developer responsibility.
2. Navigate to 'Security -> Responsibility -> Define'
3. Query for the responsibility for which you want to make the form as 'Query Only' and copy the 'Menu' name.
4. Navigate to 'Application -> Menu' and query with the menu name which we got in step 3 in 'User Menu Name'
5. Now look out for the form (Prompt) you want to make it as query only. Copy the value in the 'Function' field w.r.t the form that needs to be made as 'Query Only'.
6. Navigate to 'Application -> Funtion' and query with the function name we got in Step 5 in 'User Function Name' field.
7. Identify the correct Function for your form and then naviagate to the 'Form' tab.
8. Enter the value: QUERY_ONLY=YES in the parameters section in the Form tab, if you already have another value existing in the parameter form then you need to seperate them with a space..
Example: MODE="PROJECT" QUERY_ONLY=YES
1. Login into the System Administrator/Application Developer responsibility.
2. Navigate to 'Security -> Responsibility -> Define'
3. Query for the responsibility for which you want to make the form as 'Query Only' and copy the 'Menu' name.
4. Navigate to 'Application -> Menu' and query with the menu name which we got in step 3 in 'User Menu Name'
5. Now look out for the form (Prompt) you want to make it as query only. Copy the value in the 'Function' field w.r.t the form that needs to be made as 'Query Only'.
6. Navigate to 'Application -> Funtion' and query with the function name we got in Step 5 in 'User Function Name' field.
7. Identify the correct Function for your form and then naviagate to the 'Form' tab.
8. Enter the value: QUERY_ONLY=YES in the parameters section in the Form tab, if you already have another value existing in the parameter form then you need to seperate them with a space..
Example: MODE="PROJECT" QUERY_ONLY=YES
Tripid, deliveryid Sales order
select wt.trip_id trip,
wt.status_code sc,
oola.line_id,
wtsp.stop_id ,
wtsp.status_code sc,
wtsp.pending_interface_flag pif,
wtsd.status_code sc,
wtsd.pending_interface_flag pif,
wtsd.lock_stop_id lockid,
wnd.organization_id org,
wnd.status_code,
wnd.planned_flag pf,
wdd.delivery_detail_id wddi,
wdd.released_status rs,
wdd.inv_interfaced_flag inv,
wdd.oe_interfaced_flag oe,
ooha.order_number,
oola.flow_status_code flow_status,
oola.org_id ordou,
wdd.org_id wddou,
wdd.source_code sc,
wdd.source_line_id line_id,
wdd.source_header_id hdr_id
from wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
oe_order_headers_all ooha,
oe_order_lines_all oola,
wsh_delivery_legs wdl,
wsh_trip_stops wtsp,
wsh_trip_stops wtsd,
wsh_trips wt
where
ooha.order_number = nvl(:Order_numbe,ooha.order_number) and
wda.delivery_id(+) = wnd.delivery_id
and wdd.delivery_detail_id(+) = wda.delivery_detail_id
and ooha.header_id(+) = wdd.source_header_id
and oola.line_id(+) = wdd.source_line_id
and wdl.delivery_id (+) = wnd.delivery_id
and wtsp.stop_id (+) = wdl.pick_up_stop_id
and wtsd.stop_id (+) = wdl.drop_off_stop_id
and wt.trip_id (+) = wtsp.trip_id
order by ooha.order_number, oola.line_number;
wt.status_code sc,
oola.line_id,
wtsp.stop_id ,
wtsp.status_code sc,
wtsp.pending_interface_flag pif,
wtsd.status_code sc,
wtsd.pending_interface_flag pif,
wtsd.lock_stop_id lockid,
wnd.organization_id org,
wnd.status_code,
wnd.planned_flag pf,
wdd.delivery_detail_id wddi,
wdd.released_status rs,
wdd.inv_interfaced_flag inv,
wdd.oe_interfaced_flag oe,
ooha.order_number,
oola.flow_status_code flow_status,
oola.org_id ordou,
wdd.org_id wddou,
wdd.source_code sc,
wdd.source_line_id line_id,
wdd.source_header_id hdr_id
from wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
oe_order_headers_all ooha,
oe_order_lines_all oola,
wsh_delivery_legs wdl,
wsh_trip_stops wtsp,
wsh_trip_stops wtsd,
wsh_trips wt
where
ooha.order_number = nvl(:Order_numbe,ooha.order_number) and
wda.delivery_id(+) = wnd.delivery_id
and wdd.delivery_detail_id(+) = wda.delivery_detail_id
and ooha.header_id(+) = wdd.source_header_id
and oola.line_id(+) = wdd.source_line_id
and wdl.delivery_id (+) = wnd.delivery_id
and wtsp.stop_id (+) = wdl.pick_up_stop_id
and wtsd.stop_id (+) = wdl.drop_off_stop_id
and wt.trip_id (+) = wtsp.trip_id
order by ooha.order_number, oola.line_number;
Credit Memo Creation via API
Below Script can be used to create a Credit memo against an Invoice. This script was tested in 11i instance.
SET serveroutput ON;
DECLARE
-- This script was tested in 11i instance --
v_return_status VARCHAR2 (1);
p_count NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
v_request_id NUMBER;
v_context VARCHAR2 (2);
FUNCTION set_context( i_user_name IN VARCHAR2
,i_resp_name IN VARCHAR2
,i_org_id IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
NULL;
-- Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here
END set_context;
BEGIN
-- Setting the context ----
v_context :=
set_context ('&user', '&responsibility', 2038);
IF v_context = 'F'
THEN
DBMS_OUTPUT.put_line ('Error while setting the context');
END IF;
DBMS_OUTPUT.put_line ('2');
--- context done ------------
DBMS_OUTPUT.put_line ('Invoking Credit Memo Creation api');
ar_credit_memo_api_pub.create_request
(
-- standard api parameters
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_request_id => v_request_id,
-- credit memo request parameters
p_customer_trx_id => &inv_cust_trx_id,
p_line_credit_flag => 'N',
p_line_amount => &return_amount,
p_tax_amount => 0,
p_cm_reason_code => 'RETURN',
p_skip_workflow_flag => 'Y',
p_batch_source_name => '&batch_source_name',
p_credit_method_installments => NULL,
p_credit_method_rules => NULL
);
DBMS_OUTPUT.put_line ('Message count ' || v_msg_count);
IF v_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' || v_msg_data);
ELSIF v_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
v_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF v_msg_data IS NULL
THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || v_msg_data);
END LOOP;
END IF;
END;
SET serveroutput ON;
DECLARE
-- This script was tested in 11i instance --
v_return_status VARCHAR2 (1);
p_count NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
v_request_id NUMBER;
v_context VARCHAR2 (2);
FUNCTION set_context( i_user_name IN VARCHAR2
,i_resp_name IN VARCHAR2
,i_org_id IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
NULL;
-- Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here
END set_context;
BEGIN
-- Setting the context ----
v_context :=
set_context ('&user', '&responsibility', 2038);
IF v_context = 'F'
THEN
DBMS_OUTPUT.put_line ('Error while setting the context');
END IF;
DBMS_OUTPUT.put_line ('2');
--- context done ------------
DBMS_OUTPUT.put_line ('Invoking Credit Memo Creation api');
ar_credit_memo_api_pub.create_request
(
-- standard api parameters
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_request_id => v_request_id,
-- credit memo request parameters
p_customer_trx_id => &inv_cust_trx_id,
p_line_credit_flag => 'N',
p_line_amount => &return_amount,
p_tax_amount => 0,
p_cm_reason_code => 'RETURN',
p_skip_workflow_flag => 'Y',
p_batch_source_name => '&batch_source_name',
p_credit_method_installments => NULL,
p_credit_method_rules => NULL
);
DBMS_OUTPUT.put_line ('Message count ' || v_msg_count);
IF v_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' || v_msg_data);
ELSIF v_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
v_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF v_msg_data IS NULL
THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || v_msg_data);
END LOOP;
END IF;
END;
AR FAQs
1. What is TCA? Tables?
A) Trading Community Architecture. It is a centralized repository of business entities such as Partners, Customers, and Organizations etc. It is a new framework developed in Oracle 11i.
HZ_PARTIES: The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name. The parties can be one of four types:
Organization for example, Oracle Corporation
Person for example, Jane Doe
Group for example, World Wide Web Consortium
Relationship for example, Jane Doe at Oracle Corporation.
HZ_LOCATIONS: The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts.
HZ_PARTY_SITES: The HZ_PARTY_SITES table links a party (see HZ_PARTIES) and a location (see HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. This party site can then be used for multiple customer accounts within the same party.
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUST_CONTACT_POINTS etc.
2. What are Base Tables or Interface Tables for Customer Conversions, Autolockbox, Auto Invoice?
A) Customer Conversion:
Interface Tables : RA_CUSTOMERS_INTERFACE_ALL, RA_CUSTOMER_PROFILES_INT_ALL,
RA_CONTACT_PHONES_INT_ALL,
RA_CUSTOMER_BANKS_INT_ALL,
RA_CUST_PAY_METHOD_INT_ALL
Base Tables : RA_CUSTOMERS, RA_ADDRESSES, RA_SITE_USES_ALL,
RA_CUSTOMER_PROFILES_ALL, RA_PHONES etc
B) Auto Invoice:
Interface Tables : RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL, RA_INTERFACE_ERRORS_ALL
Base Tables : RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL,
RA_CUST_TRX_LINE_GL_DIST_ALL, RA_CUST_TRX_LINE_SALESREPS_ALL, RA_CUST_TRX_TYPES_ALL
C) AutoLockBox:
Interface Tables : AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)
Interim tables : AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation)
: AR_INTERIM_CASH_RCPT_LINES_ALL,
AR_INTERIM_POSTING
Base Tables : AR_CASH_RECEIPTS_ALL, AR_RECEIVABLE_APPLICATIONS_ALL,
AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)
3. What are the tables in which Invoices/transactions information is stored?
A) RA_CUSTOMER_TRX_ALL, The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment, bills receivable, and credit memo header information. Each row in this table includes general invoice information such as customer, transaction type, and printing instructions.
RA_CUSTOMER_TRX_LINES_ALL, The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines (LINE, FREIGHT and TAX).
RA_CUST_TRX_LINE_SALESREPS_ALL, The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry.
The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
4. What are the tables In which Receipt information is stored?
A)
AR_PAYMENT_SCHEDULES_ALL, The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.
Transaction classes determine if a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL table. Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries, such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. Using the CASH_RECEIPT_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.
AR_CASH_RECEIPTS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table.
AR_RECEIVABLE_APPLICATIONS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. Cash receipts proceed through the confirmation, remittance, and clearance steps. Each step creates rows in the AR_CASH_RECEIPT_HISTORY table.
5. What are the tables in which Accounts information is stored?
RA_CUST_TRX_LINE_GL_DIST_ALL
6. What are the different statuses for Receipts?
A) Unidentified – Lack of Customer Information
Unapplied – Lack of Transaction/Invoice specific information (Ex- Invoice Number)
Applied – When all the required information is provided.
On-Account, Non-Sufficient Funds, Stop Payment, and Reversed receipt.
7. What Customization that you have done for Autolockbox?
8. What is Autolockbox?
A) Auto lockbox is a service that commercial banks offer corporate customers to enable them to out source their account receivable payment processing. Auto lockbox can also be used to transfer receivables from previous accounting systems into current receivables. It eliminates manual data entry by automatically processing receipts that are sent directly to banks. It involves three steps
• Import (Formats data from bank file and populates the Interface Table),
• Validation(Validates the data and then Populates data into Interim Tables),
• Post Quick Cash(Applies Receipts and updates Balances in BaseTables).
9. What is Transmission Format?
A) Transmission Format specifies how data in the lockbox bank file should be organized such that it can be successfully imported into receivables interface tables. Example, Default, Convert, Cross Currency, Zengen are some of the standard formats provided by oracle.
10. What is Auto Invoice?
A) Autoinvoice is a tool used to import and validate transaction data from other financial systems and create invoices, debit-memos, credit memos, and on account credits in Oracle receivables. Using Custom Feeder programs transaction data is imported into the autoinvoice interface tables.
Autoinvoice interface program then selects data from interface tables and creates transactions in receivables (Populates receivable base tables) . Transactions with invalid information are rejected by receivables and are stored in RA_INTERFACE_ERRORS_ALL interface table.
11. What are the Mandatory Interface Tables in Auto Invoice?
RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL.
12. What are the Set up required for Custom Conversion, Autolockbox and Auto Invoice?
A) Autoinvoice program Needs AutoAccounting to be defined prior to its execution.
13. What is AutoAccounting?
A) By defining AutoAccounting we specify how the receivables should determine the general ledger accounts for transactions manually entered or imported using Autoinvoice. Receivables automatically creates default accounts(Accounting Flex field values) for revenue, tax, freight, financial charge, unbilled receivable, and unearned revenue accounts using the AutoAccounting information.
14. What are Autocash rules?
A) Autocash rules are used to determine how to apply the receipts to the customers outstanding debit items. Autocash Rule Sets are used to determine the sequence of Autocash rules that Post Quickcash uses to update the customers account balances.
15. What are Grouping Rules? (Used by Autoinvoice)
A) Grouping rules specify the attributes that must be identical for lines to appear on the same transaction. After the grouping rules are defined autoinvoice uses them to group revenues and credit transactions into invoices debit memos, and credit memos.
16. What are Line Ordering Rules? (Used by Autoinvoice)
A) Line ordering rules are used to order transaction lines when grouping the transactions into invoices, debit memos and credit memos by autoinvoice program. For instance if transactions are being imported from oracle order management , and an invoice line ordering rule for sales_order _line is created then the invoice lists the lines in the same order of lines in sales order.
17. In which table you can see the amount due of a customer?
A) AR_PAYMENT_SCHEDULES_ALL
18. How do you tie Credit Memo to the Invoice?
At table level, In RA_CUSTOMER_TRX_ALL, If you entered a credit memo, the PREVIOUS_CUSTOMER_TRX_ID column stores the customer transaction ID of the invoice that you credited. In the case of on-account credits, which are not related to any invoice when the credits are created, the PREVIOUS_CUSTOMER_TRX_ID column is null.
19. What are the available Key Flex Fields in Oracle Receivables?
A) Sales Tax Location Flex field, It’s used for sales tax calculations.
Territory Flex field is used for capturing address information.
20. What are Transaction types? Types of Transactions in AR?
A) Transaction types are used to define accounting for different transactions such as Debit Memo, Credit Memo, On-Account Credits, Charge Backs, Commitments and invoices.
Monday, September 27, 2010
Deep Drive: Customer Interface in AR
Lot of people requested some more information for customer import. So I decided to clubbed together, so here to go:
Lets start with Customer ..why it is important in your business.
As per encyclopedia the customer is defined as:
“A customer is someone who makes use of or receives the products or services of an individual or organization.” Its means it is one who become a entity in your business world, irrespective of your line of business. If you are manufacturer the customer is one to whom you provide the product and get the money or services for which your get paid.
Time to time the customer definition has been changed and now in today economy it can be redefined as:
A customer..may include users, consumers, demanders, commanders, and requestors. Any person or entity who interacts directly or indirectly with any business system, thus it can be a client within internal departments, a supplier from the procurement process, an employee, or someone who is ringing up the cash register.
What information is important to keep in Business?
Typical information required for any customer is address, contact, bank , profile,class. Oracle standard form does have more than 8 tabs which hold most of the information. A typical flow of customer setup in Oracle is as;
Fig: Standard Setup process for customer
Fig : Entity Model for Customer Setup
What is Customer Interface ?
Customer Interface is a oracle seeded tool that is used to import and validate current or historical customer information from other systems into Receivables. Once customer information is imported into the system, you can use Customer Interface to import additional data for that customer (such as additional contacts or addresses) and to update existing information. This is yet another options to enter Customer information other than manually update and enter new information using the Customer windows.
Customer Interface and Customer in pre 11i and 11i
If you are coming from some old version, if have been noticed few things has been changed:
Here is significant changes has been noticed from pre 11i and r11i version.
FIND screen
in 11i Find window automatically appears while calling customer screen.
most important , the Match Results window now is included in 11i, and it represnt multiple lines due to Parties and Accounts:
Customer screen
Customer Tables
Here is summarize information for both for them:
Considering Customer as Parties
The following diagram shows how customer information is imported into the customer tables.
11i Customer Interface Vs Oracle Base table
Here is summarize information for interface Vs base table. Once Customer Import get completed successfully , the data moved to these tables:
Please take a note, the bank model has been changed in r12, this will have till 11.5.10.2. If you are looking for R12 , refer to trm guide.
Where to start for Customer Interface
1.The first steps would be your is preparing Receivables setup activity
3. RUN the Import Program
1. Check out some of the Profile Options hitting Customer Import
Many times AR department is not like oracle seeded number which start by default 1000.Options are there:
From R11 and 11i, you cannot change the sequence via the forms and therefore any change that you make to the sequence would have to be
through SQLPlus and that would not be supported.
Oracle benchmark is about 10,000 records per batch is ideal, it is suggested to keep the batch size small.
5.When rolling out in Multi-Org , then you must populate the org_IDs in the interface tables and run the customer interface for each organization set-up responsiblity.
“Party†is an entity in the Trading Community Model that can enter into business relationships. A party is a real person, organization, branch,subsidiary, legal entity, holding company, etc. The attributes of a party are universal. In other words, they are independent of your selling (or ultimately buying) relationship with the party.
“Account†refers to the details of the deploying company’s selling relationship with a particular customer.
Account – the attributes of the selling relationship between the company deploying Oracle Applications and a party. Account attributes do not describe a
party; they only exist when a selling relationship is present between the deploying company and the party.
More over you should note that: An account is created for a party not for a party site. The selling relationship is not with a location but with the party that is using that location.
An Account cannot be created without a Party.
Account Site – a party site that is used within the context of an account; e.g., for billing or shipping purposes.
You need to have 2 tables:
1) HZ_CUST_ACCOUNTS
The HZ_CUST_ACCOUNTS table stores information about customer relationships established with a party. When a party becomes a customer, information about the customer account is stored in this table. Since a party can have multiple customer accounts, this table may contain several records for a single party. For example, an individual person may establish a personal account, a family account, and a professional account for a consulting practice. Note that the focus of this table is a business relationship and how transactions are conducted in the relationship. The primary key for this table is CUST_ACCOUNT_ID.
A single PARTY_ID is stored on this table to designate the customer account owner.
Each customer account may have only one owner (although other parties may be associated to the customer account via the HZ_CUST_ACCOUNT_ROLES table).
2)HZ_CUST_ACCT_RELATE_ALL
HZ_CUST_ACCT_RELATE_ALL stores information about relationships between customer accounts. A flag allows you to indicate whether a relationship is reciprocal. Note that columns also exist to reflect if the account relationship exist to enable shipping or billing to other accounts.
In real business world, these are approch normally we take:
1.Party Centric Approach : where each custmer is traeted as Party.
2.Site Centric Approach
Here are the screen snapshots how it looks:
1.Party Centric Approach
I have done setup for three account, account#1,#2,#3
2.Site Centric Approach
This can be best understood as customer Number 10009831-10009832-10009834
select party_id, party_number,party_name from hz_parties where party_name=’PAULINE’
Means we have 4 party created
Case 1: 3 party as 3 customer name with 3 different account
Case 2: 1 party as 1 Customer name with 3 site , each site pointing to one account.
Case 2, is more popular in industry. Typical Healthcare , uses case 2.
select * from HZ_CUST_ACCOUNTS where party_id in (select party_id from hz_parties where party_name=’PAULINE’)
These are the error message , sharing for the rest of people who seek some information.
These are the message codes and their meaning:
A1 –> The customer reference for update does not exist in RA_CUSTOMERS
A2 –> The address reference for update does not exist in RA_ADDRESSES
A3 –> Customer reference for insert is already defined in RA_CUSTOMERS
A4 –> Site use for this address reference already exists in the database
A5 –> Customer Number already assigned to a different customer
B1 –> ORIG_SYSTEM_ADDRESS_REF is mandatory when specifying an address
B2 –> ADDRESS1 is mandatory when specifying an address
B3 –> COUNTRY is mandatory when specifying an address
B4 –> SITE_USE_CODE is mandatory when inserting an address
B5 –> PRIMARY_SITE_USE_FLAG is mandatory when inserting an address
B6 –> CUSTOMER_CLASS_CODE is not defined in AR_LOOKUPS
B7 –> CUSTOMER_PROFILE_CLASS_NAME has an invalid value
B8 –> STATE is not defined in AR_LOCATION_VALUES
B9 –> COUNTRY is not defined in fnd_territories
B0 –> SITE_USE_CODE is not defined in AR_LOOKUPS
C1 –> This customer reference has two different customer names defined
C2 –> This customer reference has two different customer numbers defined
C3 –> This customer reference has two different parent customer references
C5 –> Customer reference has two different customer class codes defined
C6 –> This customer reference has two identical primary site uses defined
D1 –> Address reference has two different ADDRESS1 values
D2 –> Address reference has two different ADDRESS2 values
D3 –> Address reference has two different ADDRESS3 values
D4 –> Address reference has two different ADDRESS4 values
D5 –> Address reference has two different cities
D6 –> Address reference has two different postal codes
D7 –> Address reference has two different states
D8 –> Address reference has two different provinces
D9 –> Address reference has two different counties
D0 –> Address reference has two different countries
E1 –> Address reference has two identical site use codes
E2 –> Address reference has two different customers
F1 –> ORIG_SYSTEM_TELEPHONE_REF mandatory for telephone information
F2 –> TELEPHONE is mandatory when specifying telephone information
F3 –> TELEPHONE_TYPE is mandatory when specifying telephone information
F4 –> TELEPHONE_TYPE is not defined in AR_LOOKUPS
F5 –> Telephone reference for insert is already defined in RA_PHONES
F6 –> Telephone reference for update does not exist in RA_PHONES
G1 –> ORIG_SYSTEM_CONTACT_REF mandatory for contact information
G2 –> LAST_NAME is mandatory when specifying a contact
G3 –> CONTACT_TITLE is not defined in AR_LOOKUPS
G4 –> Contact reference for insert is already defined in RA_CONTACTS
G5 –> Contact reference for update is not defined in RA_CONTACTS
G6 –> The address reference specified is not defined for this customer
G7 –> CONTACT_JOB_TITLE must be defined in AR_LOOKUPS
H1 –> Contact reference has two different first names
H2 –> Contact reference has two different last names
H3 –> Contact reference has two different titles
H4 –> Contact reference has two different job titles
H5 –> Contact reference has two different customers
H6 –> Contact reference has two different addresses
I1 –> Telephone reference has two different phone numbers
I2 –> Telephone reference has multiple extensions
I3 –> Telephone reference has two different types
I4 –> Telephone reference has two different area codes
I6 –> Telephone reference has two different customers
I7 –> Telephone reference has two different addresses
J1 –> SITE_USE_CODE is not updateable
J2 –> PRIMARY_SITE_USE_FLAG is not updateable
J3 –> LOCATION is not updateable
J4 –> CUSTOMER_TYPE is not defined in AR_LOOKUPS
J5 –> PRIMARY_SITE_USE_FLAG has an invalid value
J6 –> CUSTOMER_NUMBER must be null when auto-numbering is set to “Yes”
J7 –> CUSTOMER_NUMBER is mandatory when auto-numbering is set to “No”
J8 –> INSERT_UPDATE_FLAG has an invalid value
J9 –> CUSTOMER_STATUS must have a value of ‘A’ or ‘I’
K1 –> Concurrent request failed
K3 –> This customer reference has two different customer types defined
L1 –> COLLECTOR_NAME is mandatory when no profile class specified
L2 –> TOLERANCE is mandatory when no profile class specified
L3 –> DISCOUNT_TERMS is mandatory when no profile class specified
L4 –> DUNNING_LETTERS is mandatory when no profile class specified
L5 –> INTEREST_CHARGES is mandatory when no profile class specified
L6 –> STATEMENTS is mandatory when no profile class specified
L7 –> CREDIT_BALANCE_STATEMENTS mandatory when no profile class specified
L9 –> DUNNING_LETTER_SET_NAME is mandatory when DUNNING_LETTERS is “Yes”
L0 –> CHARGE_ON_FINANCE_CHARGE_FLAG mandatory when INTEREST_CHARGES is “Yes”
M1 –> INTEREST_PERIOD_DAYS is mandatory when INTEREST_CHARGES is “Yes”
M3 –> COLLECTOR_NAME has an invalid value
M4 –> CREDIT_CHECKING has an invalid value
M5 –> TOLERANCE has an invalid value
M6 –> DISCOUNT_TERMS has an invalid value
M7 –> DUNNING_LETTERS has an invalid value
M8 –> INTEREST_CHARGES has an invalid value
M9 –> STATEMENTS has an invalid value
M0 –> CREDIT_BALANCE_STATEMENTS has an invalid value
N1 –> CREDIT_HOLD has an invalid value
N2 –> CREDIT_RATING has an invalid value
N3 –> RISK_CODE has an invalid value
N4 –> STANDARD_TERM_NAME which contains the payment terms has an invalid value
N5 –> OVERRIDE_TERMS has an invalid value
N6 –> DUNNING_LETTER_SET_NAME has an invalid value
N7 –> STATEMENT_CYCLE_NAME has an invalid value
N8 –> ACCOUNT_STATUS has an invalid value
N9 –> PERCENT_COLLECTABLE has an invalid value
N0 –> AUTOCASH_HIERARCHY_NAME which contains the AutoCash rule has
an invalid value
O1 –> STATEMENT_CYCLE_NAME is mandatory when STATEMENTS is “Yes”
O2 –> LOCATION must be null when auto-numbering is set to “Yes”
O3 –> LOCATION is mandatory when auto-numbering is set to “No”
O4 –> CREDIT_CHECKING is mandatory when profile class is null
O5 –> CHARGE_ON_FINANCE_CHARGE_FLAG must be null if INTEREST_CHARGES is No
O6 –> INTEREST_PERIOD_DAYS must be null if INTEREST_CHARGES is “No”
O7 –> INTEREST_PERIOD_DAYS must be greater than zero
P1 –> Postal Code is not in the defined range of system options
Q1 –> A new location was created for a value in an address segment field
Q2 –> Validation failed for the key location flexfield structure
R1 –> CUST_SHIP_VIA_CODE is not defined in ORG_FREIGHT
R2 –> CUSTOMER_CATEGORY_CODE is not defined in AR_LOOKUPS
R3 –> CUSTOMER_CATEGORY_CODE is not enabled in AR_LOOKUPS
R4 –> CUST_TAX_CODE is not defined in AR_VAT_TAX
R5 –> CUST_TAX_REFERENCE cannot be null when CUST_TAX_CODE is ‘EXEMPT’
R6 –> SITE_USE_TAX_CODE is not defined in AR_VAT_TAX
R7 –> SITE_USE_TAX_REFERENCE is required when SITE_USE_TAX_CODE is ‘EXEMPT’
R8 –> Invalid demand class code.
R9 –> SITE_SHIP_VIA_CODE not defined in ORG_FREIGHT
S1 –> The customer reference specified is invalid
S2 –> The address reference specified is invalid
S3 –> The address reference specified is not valid for this customer
S4 –> Payment Method is not defined in AR_RECEIPT_METHODS
S5 –> A bank account does not exist for the specified customer
S6 –> The end date specified cannot be before the start date
S7 –> The address specified must have an active BILL_TO site defined
T1 –> Customer payment method already active between the dates specified
T2 –> Customer site payment method already active between the dates specified
T3 –> Customer already has a primary payment method for specified dates
T4 –> Customer site has a primary payment method on the dates specified
T5 –> This customer payment method is already active in this date range
T6 –> Multiple primary payment methods defined
V2 –> The bank account specified must be of type ‘EXTERNAL’
V3 –> Customer bank account is already active between the dates specified
V4 –> Customer site bank account already active between these dates
V5 –> This customer already has primary bank account for specified dates
V6 –> Customer site can have only 1 primary bank account for the dates
specified
V7 –> Duplicate rows exist in Interface table for this Customer Bank and
date run
V8 –> Duplicate primary customer banks defined within the interface table
W1 –> BANK_NAME is mandatory when creating a new bank account
W2 –> BANK_BRANCH_NAME is mandatory when creating a new bank account
W3 –> BANK_ACCOUNT_CURRENCY_CODE is mandatory creating a new bank account
W4 –> BANK_ACCOUNT_CURRENCY_CODE is not defined in FND_CURRENCIES
W5 –> Bank number already exists.
W6 –> Duplicate bank number in interface table.
W7 –> Primary flag should be ‘Y’ or ‘N’.
W8 –> Duplicate bank and branch name in interface table.
W9 –> Duplicate Location
W0 –> Bank and branch name already exists.
X1 –> AUTO_REC_INCL_DISPUTED_FLAG mandatory when profile class is null
X2 –> TAX_PRINTING_OPTION is mandatory when no profile class specified
X3 –> GROUPING_RULE_NAME is mandatory when no profile class is specified
X4 –> CHARGE_ON_FINANCE_CHARGES_FLAG has an invalid value
X5 –> GROUPING_RULE_NAME has an invalid value
X6 –> CURRENCY_CODE has an invalid value
X7 –> CREDIT_BALANCE_STATEMENTS is mandatory when STATEMENTS is “Yes”
X8 –> CREDIT_BALANCE_STATEMENTS must be “No” when STATEMENTS is “No”
X9 –> STATEMENT_CYCLE_NAME must be null when STATEMENTS is “No”
X0 –> OVERRIDE_TERMS is mandatory when no profile class is specified
Y1 –> PARTY_NUMBER must be null when auto-numbering is set
Y2 –> PARTY_NUMBER is mandatory when auto-numbering is set to “No”
Y3 –> Party Number already assigned to a different party.
Y4 –> This party reference has two different party numbers defined in
RA_CUSTOMERS_INTERFACE.
Y5 –> PERSON_FLAG has an invalid value
Y6 –> Party Site Number already assigned to a different address
Y7 –> Address reference has two different party site numbers defined in
RA_CUSTOMERS_INTERFACE.
Y8 –> PARTY_SITE_NUMBER must be null when auto-numbering is set
Y9 –> PARTY_SITE_NUMBER is mandatory when auto-numbering is set to “No”
Z1 –> CREDIT_BALANCE_STATEMENTS must be null when STATEMENTS is null
Z2 –> STATEMENT_CYCLE_NAME must be null when STATEMENTS is null
Z3 –> CHARGE_ON_FINANCE_CHARGE_FLAG must be null when INTEREST_CHARGES is null
Z4 –> INTEREST_PERIOD_DAYS must be null when INTEREST_CHARGES is null
Z5 –> DISCOUNT_GRACE_DAYS must be null when DISCOUNT_TERMS is null
Z6 –> DISCOUNT_GRACE_DAYS must positive
Z7 –> DISCOUNT_GRACE_DAYS must be null when DISCOUNT_TERMS is “No”
Z8 –> DUNNING_LETTER_SET_NAME must be null when DUNNING_LETTERS is “No”
Z9 –> DUNNING_LETTER_SET_NAME must be null when DUNNING_LETTERS is null
Z0 –> CURRENCY_CODE is mandatory when a profile amount value is populated
a1 –> Customer record for insert must have validated profile record defined
a2 –> TAX_PRINTING_OPTION has an invalid value
a3 –> The customer profile for this customer reference already exists
a4 –> The customer profile class for update does not exist
a7 –> Duplicate record within the interface table
a8 –> Conflicting profile classes specified for this customer/site
b1 –> Both TRX_CREDIT_LIMIT and OVERALL_CREDIT_LIMIT must be populated
b2 –> TRX_CREDIT_LIMIT may not be greater than the OVERALL_CREDIT_LIMIT
b3 –> DUNNING_LETTER_SET_NAME must have a unique value
b4 –> COLLECTOR_NAME must have a unique value
b5 –> STANDARD_TERM_NAME must have a unique value
b6 –> STATEMENT_CYCLE_NAME must have a unique value
b7 –> BANK_ACCOUNT_NUM is mandatory when creating a new bank account
b8 –> AUTO_REC_INCL_DISPUTE_FLAG has an invalid value
b9 –> PAYMENT_GRACE_DAYS must be a positive value
e2 –> Bill_to_orig_address_ref should only be defined for Ship-to Addresses
e3 –> Bill_to_orig_address_ref is not a valid bill-to address
f1 –> You may have only one active Dunning site use for each customer
f2 –> For each customer, you may only have one active “Statements” type
f3 –> For each customer, you may only have one active Legal site
f4 –> Clearing Days must be greater than or equal to zero
f5 –> Address language is not installed
f6 –> Address reference has different languages
f7 –> Duplicate telephone reference in table RA_CONTACT_PHONES_INTERFACE
f8 –> A bank and branch with this bank number and branch number already exists
f9 –> Customer Prospect Code must be either CUSTOMER or PROSPECT
g1 –> This customer reference has two different customer prospect codes
u5 –> Contact reference has two different e-mail addresses
w2 –> CREDIT_CLASSIFICATION must have a valid value
w3 –> You cannot update the PARTY_TYPE using Customer Interface.
Please do not specify a value for PARTY_TYPE when the
INSERT_UPDATE_FLAG is set to U.
w4 –> When you create a PERSON party_type, you must provide
PERSON_FIRST_NAME or PERSON_LAST_NAME.
y0 –> CONTACT_JOB_TITLE is not defined
y1 –> PHONE_COUNTRY_CODE is not defined in HZ_PHONE_COUNTRY_CODES
y2 –> This customer is already assigned to a different party
y3 –> This customer is already assigned to a different party
y4 –> LOCKBOX_MATCHING_OPTION must have a valid value
y6 –> TELEPHONE_TYPE cannot be updated from telex to any other type or any
other type to telex.
y7 –> You cannot update this address. A printed, posted, or applied
transaction with an associated tax line exists for this address
y8 –> ADDRESS_CATEGORY_CODE does not exist. Please enter a valid adress
category code or define a new one using the Receivables Lookups
window.
y9 –> ADDRESS_CATEGORY_CODE is not enabled. Please enable this address
category by updating the Enabled flag in the Receivables Lookups window.
Lets start with Customer ..why it is important in your business.
As per encyclopedia the customer is defined as:
“A customer is someone who makes use of or receives the products or services of an individual or organization.” Its means it is one who become a entity in your business world, irrespective of your line of business. If you are manufacturer the customer is one to whom you provide the product and get the money or services for which your get paid.
Time to time the customer definition has been changed and now in today economy it can be redefined as:
A customer..may include users, consumers, demanders, commanders, and requestors. Any person or entity who interacts directly or indirectly with any business system, thus it can be a client within internal departments, a supplier from the procurement process, an employee, or someone who is ringing up the cash register.
What information is important to keep in Business?
Typical information required for any customer is address, contact, bank , profile,class. Oracle standard form does have more than 8 tabs which hold most of the information. A typical flow of customer setup in Oracle is as;
Fig: Standard Setup process for customer
Fig : Entity Model for Customer Setup
What is Customer Interface ?
Customer Interface is a oracle seeded tool that is used to import and validate current or historical customer information from other systems into Receivables. Once customer information is imported into the system, you can use Customer Interface to import additional data for that customer (such as additional contacts or addresses) and to update existing information. This is yet another options to enter Customer information other than manually update and enter new information using the Customer windows.
Customer Interface and Customer in pre 11i and 11i
If you are coming from some old version, if have been noticed few things has been changed:
- Customer tables have changed, to move customer in TCA model, it means
- The HZ tables
- The role of Parties
- Note:Added in order to track prospective customers Due to CRM integration and adds “benefit†of having all customer “groups†stored in one location.
- Pre 11i versions used only 12 tables
- 11i version uses 23+ tables
- Only 4 of those tables remain the same
- Main Customer tables have changed
- Revised look and feel to Customer screen, too
Here is significant changes has been noticed from pre 11i and r11i version.
FIND screenin 11i Find window automatically appears while calling customer screen.
most important , the Match Results window now is included in 11i, and it represnt multiple lines due to Parties and Accounts:
Customer screenCustomer Tables
- Previous Tables that have changed
- RA_CUSTOMERS
- RA_ADDRESSES
- RA_SITE_USES
- RA_PHONES
- RA_CONTACTS
- AR_CUSTOMER_PROFILES
- RA_CUSTOMER_RELATIONSHIPS
- AR_CUSTOMER_PROFILE_AMOUNTS
- Tables that remain the same
- RA_CUST_RECEIPT_METHODS
- AP_BANK_BRANCHES
- AP_BANK_ACCOUNTS
- AP_BANK_ACCOUNT_USES
- RA_CUSTOMERS, previously the main customer table is now a view.This become view which consists of data in HZ_CUST_ACCOUNTS and HZ_PARTIES tables.
- New Customer Tables – also known as HZ Tables
- The new HZ Customer Tables have tables for Customer Accounts and Parties
Here is summarize information for both for them:
Considering Customer as Parties
- HZ_PARTIES stores information about organizations, groups, and people.
- If a party becomes a customer then the information for the customer is stored in the HZ_CUST_ACCOUNTS table.
- A Party record in the Parties table can have multiple customer account records in the Customer Accounts table.
- One row is created in HZ_PARTIES for every customer record that is imported through the Customer Interface.
- CRM uses the customer module making it a requirement for all customers to have a party id and customer id.
The following diagram shows how customer information is imported into the customer tables.
11i Customer Interface Vs Oracle Base table
Here is summarize information for interface Vs base table. Once Customer Import get completed successfully , the data moved to these tables:
Please take a note, the bank model has been changed in r12, this will have till 11.5.10.2. If you are looking for R12 , refer to trm guide.
Where to start for Customer Interface
1.The first steps would be your is preparing Receivables setup activity
- Be sure to set up new data in Receivables that the Customer Interface should import. For example:
- AutoCash Rule Sets
- AutoInvoice Grouping Rules
- Collectors
- Customer Addresses
- Customer Bank Information
- Customer Exemptions
- Customer Profile Classes
- Demand Classes
- Dunning Letter Sets
- Freight Carriers
- Payment Methods
- Payment Terms
- Statement Cycles
- Tax Codes
- Be sure to also set up Lookups in Receivables that the Customer Interface should import. These are the lookups:
- Countries
- Site Use Codes
- Credit Ratings
- Risk Codes
- Account Statuses
- Communication Types
- Customer Classes
- RA_CUSTOMER_INTERFACE_ALL
- ORIG_SYSTEM_CUSTOMER_REF
- CUSTOMER_NAME
- CUSTOMER_STATUS
- INSERT_UPDATE_FLAG
- CUSTOMER_NUMBER
- ORIG_SYSTEM_ADDRESS_REF
- PRIMARY_SITE_USE_FLAG
- SITE_USE_CODE
- ADDRESS1
- COUNTRY
- LOCATION
- RA_CUSTOMER_PROFILES_INT_ALL
- CUSTOMER_PROFILE_CLASS_NAME
- ORIG_SYSTEM_CUSTOMER_REF
- INSERT_UPDATE_FLAG
- CREDIT_HOLD
- ORIG_SYSTEM_ADDRESS_REF
- RA_CONTACT_PHONES_INT_ALL
- ORIG_SYSTEM_CUSTOMER_REF
- ORIG_SYSTEM_TELEPHONE_REF
- TELEPHONE
- TELEPHONE_TYPE
- INSERT_UPDATE_FLAG
- ORIG_SYSTEM_ADDRESS_REF
- ORIG_SYSTEM_CONTACT_REF
- CONTACT_LAST_NAME
- RA_BANKS_INTERFACE
- ORIG_SYSTEM_CUSTOMER_REF
- PRIMARY_FLAG
- START_DATE
- BANK_ACCOUNT_NAME
- BANK_ACCOUNT_CURRENCY_CODE
- BANK_ACCOUNT_NUM
- BANK_BRANCH_NAME
- ORIG_SYSTEM_ADDRESS_REF
- RA_CUST_PAY_METHOD_INTERFACE
- ORIG_SYSTEM_CUSTOMER_REF
- START_DATE
- PAYMENT_METHOD_NAME
- PRIMARY_FLAG
- ORIG_SYSTEM_ADDRESS_REF
- Run Import after AR Customer Interface tables have been populated
- Program will validate the data in the interface table before creating records in Receivables
- Run the Customer Interface process through the Submit Request window
- But, a separate navigational path is also provided
Interfaces -> Customer - Check output file for errors
- Make corrections and repeat import process
- a3: Bill_To_Orig_Address_Ref is not a valid bill-to address
- Verify the Bill-To address reference is valid. Keep in mind that when using the bill-to reference with a ship-to address record… the bill-to must already exist in Receivables.
- Note: Ran into this issue. Try running bill-to records through the interface first and ship-to records as second batch – this will resolve the error. Do not Interface with both in the same batch.
- a1:Customer record for insert must have validated profile record defined
- New customers and each Bill-To record must have a customer level profile in the RA_CUSTOMER_PROFILES_INT_ALL table.
- a8: Conflicting profile classes specified for this customer/site
- Profile classes for customer and bill-to must be the same. Sites cannot have a profile class different from the customer.
- J1: Site_USE_CODE is not updateable.
- J3: LOCATION is not updateable.
- J2: PRIMARY_SITE_USE_FLAG is not updateable.
- Keep in mind that site_use_code, primary_use_flag, and location may not be updateable through the Customer Interface
- A3: Customer reference for insert is already defined.
- A5: Customer Number already assigned to a different customer.
- Customer reference and Customer number are values that must be unique. Verify the customer reference or customer number does not already exist for another customer.
1. Check out some of the Profile Options hitting Customer Import
- HZ: Generate Party Number
- This the profile option can be updated at Site, Application, Responsibility and User levels.This profile option determines whether party number should be auto-generated. If value is ‘No’,means party number must be passed in by the user else if ‘Yes’ or if the value is not set, party number will be auto-generated.
- HZ: Generate Party Site Number
- same as above for party site number set at all leval.
- HZ: Internal Party
- This profile option is used as a part of CRM setup. This must be set if CRM is installed. It is used for data migration purpose.
- HZ: Generate Contact Number
- This profile option determines whether contact number should be auto-generated.If the value is ‘No’, contact number must be passed in by the user. If the value is ‘Yes’ or if the value is not set, contact number will be auto-generated.
Many times AR department is not like oracle seeded number which start by default 1000.Options are there:
From R11 and 11i, you cannot change the sequence via the forms and therefore any change that you make to the sequence would have to be
through SQLPlus and that would not be supported.
To set the sequence number3. When doing Migration from other system, adviced to use TRIM Function
Step 1. In the Application Developer responsibility,To set automatic numbering for customer after setting the sequence:
Menu: Application=>Database=>Sequence
Step 2. Query on sequence RA_CUSTOMERS_NUM_SThis will bring up the sequence for the customer numbers and you can enter the number that you want it to start from.
Step 1. Menu:=>System=>System Options
Step 2. Region – Invoicing and Customers
Step 3. Check the box for Automatic Customer Numbering.
- When loading interface tables remove all trailing spaces from import data.
Example: LTRIM(RTRIM(customer_name))
Oracle benchmark is about 10,000 records per batch is ideal, it is suggested to keep the batch size small.
5.When rolling out in Multi-Org , then you must populate the org_IDs in the interface tables and run the customer interface for each organization set-up responsiblity.
“Party†is an entity in the Trading Community Model that can enter into business relationships. A party is a real person, organization, branch,subsidiary, legal entity, holding company, etc. The attributes of a party are universal. In other words, they are independent of your selling (or ultimately buying) relationship with the party.
“Account†refers to the details of the deploying company’s selling relationship with a particular customer.
Account – the attributes of the selling relationship between the company deploying Oracle Applications and a party. Account attributes do not describe a
party; they only exist when a selling relationship is present between the deploying company and the party.
More over you should note that: An account is created for a party not for a party site. The selling relationship is not with a location but with the party that is using that location.
An Account cannot be created without a Party.
Account Site – a party site that is used within the context of an account; e.g., for billing or shipping purposes.
You need to have 2 tables:
1) HZ_CUST_ACCOUNTS
The HZ_CUST_ACCOUNTS table stores information about customer relationships established with a party. When a party becomes a customer, information about the customer account is stored in this table. Since a party can have multiple customer accounts, this table may contain several records for a single party. For example, an individual person may establish a personal account, a family account, and a professional account for a consulting practice. Note that the focus of this table is a business relationship and how transactions are conducted in the relationship. The primary key for this table is CUST_ACCOUNT_ID.
A single PARTY_ID is stored on this table to designate the customer account owner.
Each customer account may have only one owner (although other parties may be associated to the customer account via the HZ_CUST_ACCOUNT_ROLES table).
2)HZ_CUST_ACCT_RELATE_ALL
HZ_CUST_ACCT_RELATE_ALL stores information about relationships between customer accounts. A flag allows you to indicate whether a relationship is reciprocal. Note that columns also exist to reflect if the account relationship exist to enable shipping or billing to other accounts.
In real business world, these are approch normally we take:
1.Party Centric Approach : where each custmer is traeted as Party.
2.Site Centric Approach
Here are the screen snapshots how it looks:
1.Party Centric Approach
I have done setup for three account, account#1,#2,#3
2.Site Centric Approach
This can be best understood as customer Number 10009831-10009832-10009834
select party_id, party_number,party_name from hz_parties where party_name=’PAULINE’
Means we have 4 party created
Case 1: 3 party as 3 customer name with 3 different account
Case 2: 1 party as 1 Customer name with 3 site , each site pointing to one account.
Case 2, is more popular in industry. Typical Healthcare , uses case 2.
select * from HZ_CUST_ACCOUNTS where party_id in (select party_id from hz_parties where party_name=’PAULINE’)
These are the error message , sharing for the rest of people who seek some information.
These are the message codes and their meaning:
A1 –> The customer reference for update does not exist in RA_CUSTOMERS
A2 –> The address reference for update does not exist in RA_ADDRESSES
A3 –> Customer reference for insert is already defined in RA_CUSTOMERS
A4 –> Site use for this address reference already exists in the database
A5 –> Customer Number already assigned to a different customer
B1 –> ORIG_SYSTEM_ADDRESS_REF is mandatory when specifying an address
B2 –> ADDRESS1 is mandatory when specifying an address
B3 –> COUNTRY is mandatory when specifying an address
B4 –> SITE_USE_CODE is mandatory when inserting an address
B5 –> PRIMARY_SITE_USE_FLAG is mandatory when inserting an address
B6 –> CUSTOMER_CLASS_CODE is not defined in AR_LOOKUPS
B7 –> CUSTOMER_PROFILE_CLASS_NAME has an invalid value
B8 –> STATE is not defined in AR_LOCATION_VALUES
B9 –> COUNTRY is not defined in fnd_territories
B0 –> SITE_USE_CODE is not defined in AR_LOOKUPS
C1 –> This customer reference has two different customer names defined
C2 –> This customer reference has two different customer numbers defined
C3 –> This customer reference has two different parent customer references
C5 –> Customer reference has two different customer class codes defined
C6 –> This customer reference has two identical primary site uses defined
D1 –> Address reference has two different ADDRESS1 values
D2 –> Address reference has two different ADDRESS2 values
D3 –> Address reference has two different ADDRESS3 values
D4 –> Address reference has two different ADDRESS4 values
D5 –> Address reference has two different cities
D6 –> Address reference has two different postal codes
D7 –> Address reference has two different states
D8 –> Address reference has two different provinces
D9 –> Address reference has two different counties
D0 –> Address reference has two different countries
E1 –> Address reference has two identical site use codes
E2 –> Address reference has two different customers
F1 –> ORIG_SYSTEM_TELEPHONE_REF mandatory for telephone information
F2 –> TELEPHONE is mandatory when specifying telephone information
F3 –> TELEPHONE_TYPE is mandatory when specifying telephone information
F4 –> TELEPHONE_TYPE is not defined in AR_LOOKUPS
F5 –> Telephone reference for insert is already defined in RA_PHONES
F6 –> Telephone reference for update does not exist in RA_PHONES
G1 –> ORIG_SYSTEM_CONTACT_REF mandatory for contact information
G2 –> LAST_NAME is mandatory when specifying a contact
G3 –> CONTACT_TITLE is not defined in AR_LOOKUPS
G4 –> Contact reference for insert is already defined in RA_CONTACTS
G5 –> Contact reference for update is not defined in RA_CONTACTS
G6 –> The address reference specified is not defined for this customer
G7 –> CONTACT_JOB_TITLE must be defined in AR_LOOKUPS
H1 –> Contact reference has two different first names
H2 –> Contact reference has two different last names
H3 –> Contact reference has two different titles
H4 –> Contact reference has two different job titles
H5 –> Contact reference has two different customers
H6 –> Contact reference has two different addresses
I1 –> Telephone reference has two different phone numbers
I2 –> Telephone reference has multiple extensions
I3 –> Telephone reference has two different types
I4 –> Telephone reference has two different area codes
I6 –> Telephone reference has two different customers
I7 –> Telephone reference has two different addresses
J1 –> SITE_USE_CODE is not updateable
J2 –> PRIMARY_SITE_USE_FLAG is not updateable
J3 –> LOCATION is not updateable
J4 –> CUSTOMER_TYPE is not defined in AR_LOOKUPS
J5 –> PRIMARY_SITE_USE_FLAG has an invalid value
J6 –> CUSTOMER_NUMBER must be null when auto-numbering is set to “Yes”
J7 –> CUSTOMER_NUMBER is mandatory when auto-numbering is set to “No”
J8 –> INSERT_UPDATE_FLAG has an invalid value
J9 –> CUSTOMER_STATUS must have a value of ‘A’ or ‘I’
K1 –> Concurrent request failed
K3 –> This customer reference has two different customer types defined
L1 –> COLLECTOR_NAME is mandatory when no profile class specified
L2 –> TOLERANCE is mandatory when no profile class specified
L3 –> DISCOUNT_TERMS is mandatory when no profile class specified
L4 –> DUNNING_LETTERS is mandatory when no profile class specified
L5 –> INTEREST_CHARGES is mandatory when no profile class specified
L6 –> STATEMENTS is mandatory when no profile class specified
L7 –> CREDIT_BALANCE_STATEMENTS mandatory when no profile class specified
L9 –> DUNNING_LETTER_SET_NAME is mandatory when DUNNING_LETTERS is “Yes”
L0 –> CHARGE_ON_FINANCE_CHARGE_FLAG mandatory when INTEREST_CHARGES is “Yes”
M1 –> INTEREST_PERIOD_DAYS is mandatory when INTEREST_CHARGES is “Yes”
M3 –> COLLECTOR_NAME has an invalid value
M4 –> CREDIT_CHECKING has an invalid value
M5 –> TOLERANCE has an invalid value
M6 –> DISCOUNT_TERMS has an invalid value
M7 –> DUNNING_LETTERS has an invalid value
M8 –> INTEREST_CHARGES has an invalid value
M9 –> STATEMENTS has an invalid value
M0 –> CREDIT_BALANCE_STATEMENTS has an invalid value
N1 –> CREDIT_HOLD has an invalid value
N2 –> CREDIT_RATING has an invalid value
N3 –> RISK_CODE has an invalid value
N4 –> STANDARD_TERM_NAME which contains the payment terms has an invalid value
N5 –> OVERRIDE_TERMS has an invalid value
N6 –> DUNNING_LETTER_SET_NAME has an invalid value
N7 –> STATEMENT_CYCLE_NAME has an invalid value
N8 –> ACCOUNT_STATUS has an invalid value
N9 –> PERCENT_COLLECTABLE has an invalid value
N0 –> AUTOCASH_HIERARCHY_NAME which contains the AutoCash rule has
an invalid value
O1 –> STATEMENT_CYCLE_NAME is mandatory when STATEMENTS is “Yes”
O2 –> LOCATION must be null when auto-numbering is set to “Yes”
O3 –> LOCATION is mandatory when auto-numbering is set to “No”
O4 –> CREDIT_CHECKING is mandatory when profile class is null
O5 –> CHARGE_ON_FINANCE_CHARGE_FLAG must be null if INTEREST_CHARGES is No
O6 –> INTEREST_PERIOD_DAYS must be null if INTEREST_CHARGES is “No”
O7 –> INTEREST_PERIOD_DAYS must be greater than zero
P1 –> Postal Code is not in the defined range of system options
Q1 –> A new location was created for a value in an address segment field
Q2 –> Validation failed for the key location flexfield structure
R1 –> CUST_SHIP_VIA_CODE is not defined in ORG_FREIGHT
R2 –> CUSTOMER_CATEGORY_CODE is not defined in AR_LOOKUPS
R3 –> CUSTOMER_CATEGORY_CODE is not enabled in AR_LOOKUPS
R4 –> CUST_TAX_CODE is not defined in AR_VAT_TAX
R5 –> CUST_TAX_REFERENCE cannot be null when CUST_TAX_CODE is ‘EXEMPT’
R6 –> SITE_USE_TAX_CODE is not defined in AR_VAT_TAX
R7 –> SITE_USE_TAX_REFERENCE is required when SITE_USE_TAX_CODE is ‘EXEMPT’
R8 –> Invalid demand class code.
R9 –> SITE_SHIP_VIA_CODE not defined in ORG_FREIGHT
S1 –> The customer reference specified is invalid
S2 –> The address reference specified is invalid
S3 –> The address reference specified is not valid for this customer
S4 –> Payment Method is not defined in AR_RECEIPT_METHODS
S5 –> A bank account does not exist for the specified customer
S6 –> The end date specified cannot be before the start date
S7 –> The address specified must have an active BILL_TO site defined
T1 –> Customer payment method already active between the dates specified
T2 –> Customer site payment method already active between the dates specified
T3 –> Customer already has a primary payment method for specified dates
T4 –> Customer site has a primary payment method on the dates specified
T5 –> This customer payment method is already active in this date range
T6 –> Multiple primary payment methods defined
V2 –> The bank account specified must be of type ‘EXTERNAL’
V3 –> Customer bank account is already active between the dates specified
V4 –> Customer site bank account already active between these dates
V5 –> This customer already has primary bank account for specified dates
V6 –> Customer site can have only 1 primary bank account for the dates
specified
V7 –> Duplicate rows exist in Interface table for this Customer Bank and
date run
V8 –> Duplicate primary customer banks defined within the interface table
W1 –> BANK_NAME is mandatory when creating a new bank account
W2 –> BANK_BRANCH_NAME is mandatory when creating a new bank account
W3 –> BANK_ACCOUNT_CURRENCY_CODE is mandatory creating a new bank account
W4 –> BANK_ACCOUNT_CURRENCY_CODE is not defined in FND_CURRENCIES
W5 –> Bank number already exists.
W6 –> Duplicate bank number in interface table.
W7 –> Primary flag should be ‘Y’ or ‘N’.
W8 –> Duplicate bank and branch name in interface table.
W9 –> Duplicate Location
W0 –> Bank and branch name already exists.
X1 –> AUTO_REC_INCL_DISPUTED_FLAG mandatory when profile class is null
X2 –> TAX_PRINTING_OPTION is mandatory when no profile class specified
X3 –> GROUPING_RULE_NAME is mandatory when no profile class is specified
X4 –> CHARGE_ON_FINANCE_CHARGES_FLAG has an invalid value
X5 –> GROUPING_RULE_NAME has an invalid value
X6 –> CURRENCY_CODE has an invalid value
X7 –> CREDIT_BALANCE_STATEMENTS is mandatory when STATEMENTS is “Yes”
X8 –> CREDIT_BALANCE_STATEMENTS must be “No” when STATEMENTS is “No”
X9 –> STATEMENT_CYCLE_NAME must be null when STATEMENTS is “No”
X0 –> OVERRIDE_TERMS is mandatory when no profile class is specified
Y1 –> PARTY_NUMBER must be null when auto-numbering is set
Y2 –> PARTY_NUMBER is mandatory when auto-numbering is set to “No”
Y3 –> Party Number already assigned to a different party.
Y4 –> This party reference has two different party numbers defined in
RA_CUSTOMERS_INTERFACE.
Y5 –> PERSON_FLAG has an invalid value
Y6 –> Party Site Number already assigned to a different address
Y7 –> Address reference has two different party site numbers defined in
RA_CUSTOMERS_INTERFACE.
Y8 –> PARTY_SITE_NUMBER must be null when auto-numbering is set
Y9 –> PARTY_SITE_NUMBER is mandatory when auto-numbering is set to “No”
Z1 –> CREDIT_BALANCE_STATEMENTS must be null when STATEMENTS is null
Z2 –> STATEMENT_CYCLE_NAME must be null when STATEMENTS is null
Z3 –> CHARGE_ON_FINANCE_CHARGE_FLAG must be null when INTEREST_CHARGES is null
Z4 –> INTEREST_PERIOD_DAYS must be null when INTEREST_CHARGES is null
Z5 –> DISCOUNT_GRACE_DAYS must be null when DISCOUNT_TERMS is null
Z6 –> DISCOUNT_GRACE_DAYS must positive
Z7 –> DISCOUNT_GRACE_DAYS must be null when DISCOUNT_TERMS is “No”
Z8 –> DUNNING_LETTER_SET_NAME must be null when DUNNING_LETTERS is “No”
Z9 –> DUNNING_LETTER_SET_NAME must be null when DUNNING_LETTERS is null
Z0 –> CURRENCY_CODE is mandatory when a profile amount value is populated
a1 –> Customer record for insert must have validated profile record defined
a2 –> TAX_PRINTING_OPTION has an invalid value
a3 –> The customer profile for this customer reference already exists
a4 –> The customer profile class for update does not exist
a7 –> Duplicate record within the interface table
a8 –> Conflicting profile classes specified for this customer/site
b1 –> Both TRX_CREDIT_LIMIT and OVERALL_CREDIT_LIMIT must be populated
b2 –> TRX_CREDIT_LIMIT may not be greater than the OVERALL_CREDIT_LIMIT
b3 –> DUNNING_LETTER_SET_NAME must have a unique value
b4 –> COLLECTOR_NAME must have a unique value
b5 –> STANDARD_TERM_NAME must have a unique value
b6 –> STATEMENT_CYCLE_NAME must have a unique value
b7 –> BANK_ACCOUNT_NUM is mandatory when creating a new bank account
b8 –> AUTO_REC_INCL_DISPUTE_FLAG has an invalid value
b9 –> PAYMENT_GRACE_DAYS must be a positive value
e2 –> Bill_to_orig_address_ref should only be defined for Ship-to Addresses
e3 –> Bill_to_orig_address_ref is not a valid bill-to address
f1 –> You may have only one active Dunning site use for each customer
f2 –> For each customer, you may only have one active “Statements” type
f3 –> For each customer, you may only have one active Legal site
f4 –> Clearing Days must be greater than or equal to zero
f5 –> Address language is not installed
f6 –> Address reference has different languages
f7 –> Duplicate telephone reference in table RA_CONTACT_PHONES_INTERFACE
f8 –> A bank and branch with this bank number and branch number already exists
f9 –> Customer Prospect Code must be either CUSTOMER or PROSPECT
g1 –> This customer reference has two different customer prospect codes
u5 –> Contact reference has two different e-mail addresses
w2 –> CREDIT_CLASSIFICATION must have a valid value
w3 –> You cannot update the PARTY_TYPE using Customer Interface.
Please do not specify a value for PARTY_TYPE when the
INSERT_UPDATE_FLAG is set to U.
w4 –> When you create a PERSON party_type, you must provide
PERSON_FIRST_NAME or PERSON_LAST_NAME.
y0 –> CONTACT_JOB_TITLE is not defined
y1 –> PHONE_COUNTRY_CODE is not defined in HZ_PHONE_COUNTRY_CODES
y2 –> This customer is already assigned to a different party
y3 –> This customer is already assigned to a different party
y4 –> LOCKBOX_MATCHING_OPTION must have a valid value
y6 –> TELEPHONE_TYPE cannot be updated from telex to any other type or any
other type to telex.
y7 –> You cannot update this address. A printed, posted, or applied
transaction with an associated tax line exists for this address
y8 –> ADDRESS_CATEGORY_CODE does not exist. Please enter a valid adress
category code or define a new one using the Receivables Lookups
window.
y9 –> ADDRESS_CATEGORY_CODE is not enabled. Please enable this address
category by updating the Enabled flag in the Receivables Lookups window.
How to export and import OAF XML customization
1. Find the name of the page having required personalization
Click the "about this page" link at the bottom of the page Copy the full page name (including path)
2. For every page - find a list of customizations
sqlplus apps/
set lines 300
set serveroutput on
exec dbms_output.enable(1000000);
exec jdr_utils.listCustomizations('');
exit;
3. Download customizations found in the previous step
The following should be done for every customization:
java oracle.jrad.tools.xml.exporter.XMLExporter "" -username "apps" -password -dbconnection "(description=(address_list=(address=(community=tcp.world)(protocol=tcp)(host= port=)))(connect_data=(sid=)))" –rootdir - source database host - source database port - source database SID This will create a file .xml in current directory
4. Upload customizations to other environment
The following should be done for every customization downloaded in previous step:
java oracle.jrad.tools.xml.importer.XMLImporter.xml -username "apps" -password -dbconnection "(description=(address_list=(address=(community=tcp.world)(protocol=tcp)(host=)(port=)))(connect_data=(sid=)))" -rootdir . -rootPackage
- target database host
- target database port
- target database SID
Click the "about this page" link at the bottom of the page Copy the full page name (including path)
2. For every page - find a list of customizations
sqlplus apps/
set lines 300
set serveroutput on
exec dbms_output.enable(1000000);
exec jdr_utils.listCustomizations('
exit;
3. Download customizations found in the previous step
The following should be done for every customization:
java oracle.jrad.tools.xml.exporter.XMLExporter "
4. Upload customizations to other environment
The following should be done for every customization downloaded in previous step:
java oracle.jrad.tools.xml.importer.XMLImporter
Technical Queries Related to Oracle Purchasing
Techanical Queries Related To Oracle Purchasing
1] TO LIST OUT ALL CANCEL REQUISITIONS:-
>>list My cancel Requistionselect prh.REQUISITION_HEADER_ID, prh.PREPARER_ID , prh.SEGMENT1 "REQ NUM", trunc(prh.CREATION_DATE), prh.DESCRIPTION, prh.NOTE_TO_AUTHORIZERfrom apps.Po_Requisition_headers_all prh, apps.po_action_history pah where Action_code='CANCEL' and pah.object_type_code='REQUISITION'and pah.object_id=prh.REQUISITION_HEADER_ID
2] TO LIST ALL INTERNAL REQUISITIONS THAT DO NOT HAVE AN ASSOCIATED INTERNAL SALES ORDER
>> Select RQH.SEGMENT1 REQ_NUM,RQL.LINE_NUM,RQL.REQUISITION_HEADER_ID ,RQL.REQUISITION_LINE_ID,RQL.ITEM_ID ,RQL.UNIT_MEAS_LOOKUP_CODE ,RQL.UNIT_PRICE ,RQL.QUANTITY ,RQL.QUANTITY_CANCELLED,RQL.QUANTITY_DELIVERED ,RQL.CANCEL_FLAG ,RQL.SOURCE_TYPE_CODE ,RQL.SOURCE_ORGANIZATION_ID ,RQL.DESTINATION_ORGANIZATION_ID,RQH.TRANSFERRED_TO_OE_FLAGfromPO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQHwhereRQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_IDand RQL.SOURCE_TYPE_CODE = 'INVENTORY'and RQL.SOURCE_ORGANIZATION_ID is not nulland not exists (select 'existing internal order'from OE_ORDER_LINES_ALL LINwhere LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_IDand LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;
3] Display what requisition and PO are linked(Relation with Requisition and PO )
>> select r.segment1 "Req Num", p.segment1 "PO Num"from po_headers_all p, po_distributions_all d,po_req_distributions_all rd, po_requisition_lines_all rl,po_requisition_headers_all r where p.po_header_id = d.po_header_id and d.req_distribution_id = rd.distribution_id and rd.requisition_line_id = rl.requisition_line_id and rl.requisition_header_id = r.requisition_header_id
4] List all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO. (Purchase Requisition without a Purchase Order)
>> select prh.segment1 "PR NUM", trunc(prh.creation_date) "CREATED ON", trunc(prl.creation_date) "Line Creation Date" , prl.line_num "Seq #", msi.segment1 "Item Num", prl.item_description "Description", prl.quantity "Qty", trunc(prl.need_by_date) "Required By", ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER" from po.po_requisition_headers_all prh, po.po_requisition_lines_all prl, apps.per_people_f ppf1, (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, po.po_req_distributions_all prd, inv.mtl_system_items_b msi, po.po_line_locations_all pll, po.po_lines_all pl, po.po_headers_all ph WHERE prh.requisition_header_id = prl.requisition_header_id and prl.requisition_line_id = prd.requisition_line_id and ppf1.person_id = prh.preparer_id and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date and ppf2.agent_id(+) = msi.buyer_id and msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id and pll.line_location_id(+) = prl.line_location_id and pll.po_header_id = ph.po_header_id(+) AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) AND PRH.AUTHORIZATION_STATUS = 'APPROVED' AND PLL.LINE_LOCATION_ID IS NULL AND PRL.CLOSED_CODE IS NULL AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
ORDER BY 1,2
5] list all information form PR to PO …as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.
>> LIST AND ALL DATA ENTRY FROM PR TILL POselect distinct u.description "Requestor", porh.segment1 as "Req Number", trunc(porh.Creation_Date) "Created On", pord.LAST_UPDATED_BY, porh.Authorization_Status "Status", porh.Description "Description", poh.segment1 "PO Number", trunc(poh.Creation_date) "PO Creation Date", poh.AUTHORIZATION_STATUS "PO Status", trunc(poh.Approved_Date) "Approved Date"from apps.po_headers_all poh, apps.po_distributions_all pod, apps.po_req_distributions_all pord, apps.po_requisition_lines_all porl, apps.po_requisition_headers_all porh, apps.fnd_user u where porh.requisition_header_id = porl.requisition_header_id and porl.requisition_line_id = pord.requisition_line_id and pord.distribution_id = pod.req_distribution_id(+) and pod.po_header_id = poh.po_header_id(+) and porh.created_by = u.user_id
order by 2
6] Identifying all PO’s which does not have any PR’s
>>LIST ALL PURCHASE REQUISITION WITHOUT A PURCHASE ORDER THAT MEANS A PR HAS NOT BEEN AUTOCREATED TO PO. select prh.segment1 "PR NUM", trunc(prh.creation_date) "CREATED ON", trunc(prl.creation_date) "Line Creation Date" , prl.line_num "Seq #", msi.segment1 "Item Num", prl.item_description "Description", prl.quantity "Qty", trunc(prl.need_by_date) "Required By", ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER" from po.po_requisition_headers_all prh, po.po_requisition_lines_all prl, apps.per_people_f ppf1, (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, po.po_req_distributions_all prd, inv.mtl_system_items_b msi, po.po_line_locations_all pll, po.po_lines_all pl, po.po_headers_all ph WHERE prh.requisition_header_id = prl.requisition_header_id and prl.requisition_line_id = prd.requisition_line_id and ppf1.person_id = prh.preparer_id and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date and ppf2.agent_id(+) = msi.buyer_id and msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id and pll.line_location_id(+) = prl.line_location_id and pll.po_header_id = ph.po_header_id(+) AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) AND PRH.AUTHORIZATION_STATUS = 'APPROVED' AND PLL.LINE_LOCATION_ID IS NULL AND PRL.CLOSED_CODE IS NULL AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y' ORDER BY 1,2
7] Relation between Requisition and PO tables
>>Here is link:
PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_IDPO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_IDPO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1
What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.
--You need to find table which hold PO Approval path…
These two table keeps the data:
PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES
8] List all the PO’s with there approval ,invoice and Payment Details
>>LIST AND PO WITH THERE APPROVAL , INVOICE AND PAYMENT DETAILSselect a.org_id "ORG ID", E.SEGMENT1 "VENDOR NUM",e.vendor_name "SUPPLIER NAME",UPPER(e.vendor_type_lookup_code) "VENDOR TYPE", f.vendor_site_code "VENDOR SITE CODE",f.ADDRESS_LINE1 "ADDRESS",f.city "CITY",f.country "COUNTRY", to_char(trunc(d.CREATION_DATE)) "PO Date", d.segment1 "PO NUM",d.type_lookup_code "PO Type", c.quantity_ordered "QTY ORDERED", c.quantity_cancelled "QTY CANCELLED", g.item_id "ITEM ID" , g.item_description "ITEM DESCRIPTION",g.unit_price "UNIT PRICE", (NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount", (select decode(ph.approved_FLAG, 'Y', 'Approved') from po.po_headers_all ph where ph.po_header_ID = d.po_header_id)"PO Approved?", a.invoice_type_lookup_code "INVOICE TYPE",a.invoice_amount "INVOICE AMOUNT", to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE", a.invoice_num "INVOICE NUMBER", (select decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') from ap.ap_invoice_distributions_all x where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?", a.amount_paid,h.amount, h.check_id, h.invoice_payment_id "Payment Id", i.check_number "Cheque Number", to_char(trunc(i.check_DATE)) "PAYMENT DATE" FROM AP.AP_INVOICES_ALL A, AP.AP_INVOICE_DISTRIBUTIONS_ALL B, PO.PO_DISTRIBUTIONS_ALL C, PO.PO_HEADERS_ALL D, PO.PO_VENDORS E, PO.PO_VENDOR_SITES_ALL F, PO.PO_LINES_ALL G, AP.AP_INVOICE_PAYMENTS_ALL H, AP.AP_CHECKS_ALL I where a.invoice_id = b.invoice_id and b.po_distribution_id = c. po_distribution_id (+) and c.po_header_id = d.po_header_id (+) and e.vendor_id (+) = d.VENDOR_ID and f.vendor_site_id (+) = d.vendor_site_id and d.po_header_id = g.po_header_id and c.po_line_id = g.po_line_id and a.invoice_id = h.invoice_id and h.check_id = i.check_id and f.vendor_site_id = i.vendor_site_id and c.PO_HEADER_ID is not null and a.payment_status_flag = 'Y'
and d.type_lookup_code != 'BLANKET'
10] To know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..
The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.
The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.
These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) isPurchasing.
Budgetary Records from PO (These include reservations, reversals and cancellations):
REFERENCE_1- Source (PO or REQ)
REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id orpo_requisition_headers_all.requisition_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id orpo_req_distributions_all.distribution_id)
REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 orpo_requisition_headers_all.segment1)
REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)
Accrual Records from PO:
REFERENCE_1- Source (PO)
REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)
Take a note for Period end accruals, the REFERENCE_5 column is not used.
11] List all open PO'S
>> select h.segment1 "PO NUM", h.authorization_status "STATUS", l.line_num "SEQ NUM", ll.line_location_id, d.po_distribution_id , h.type_lookup_code "TYPE" from po.po_headers_all h, po.po_lines_all l, po.po_line_locations_all ll, po.po_distributions_all d where h.po_header_id = l.po_header_id and ll.po_line_id = l.po_Line_id and ll.line_location_id = d.line_location_id and h.closed_date is null
and h.type_lookup_code not in ('QUOTATION')
12] There are different authorization_status can a requisition have.
Approved
Cancelled
In Process
Incomplete
Pre-Approved
Rejected
and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it’s closed_code becomes ‘FINALLY CLOSED’.
13] A standard Quotations one that you can tie back to a PO.Navigate to RFQ -> Auto create -> enter a PO and reference it back.
14] To debug for a PO , where should I start.Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.
Stage 1: PO Creation :
PO_HEADERS_ALL
select po_header_id from po_headers_all where segment1 =
select * from po_headers_all where po_header_id =
po_lines_all
select * from po_lines_all where po_header_id =
po_line_locations_all
select * from po_line_locations_all where po_header_id =
po_distributions_all
select * from po_distributions_all where po_header_id =
po_releases_all
SELECT * FROM po_releases_all WHERE po_header_id =
Stage 2: Once PO is received data is moved to respective receving tables and inventory tables
RCV_SHIPMENT_HEADERS
select * from rcv_shipment_headers where shipment_header_id in(select shipment_header_id from rcv_shipment_lineswhere po_header_id =
RCV_SHIPMENT_LINES
select * from rcv_shipment_lines where po_header_id =
RCV_TRANSACTIONS
select * from rcv_transactions where po_header_id =
RCV_ACCOUNTING_EVENTS
SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN(select transaction_id from rcv_transactionswhere po_header_id =
RCV_RECEIVING_SUB_LEDGER
select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =
RCV_SUB_LEDGER_DETAILS
select * from rcv_sub_ledger_detailswhere rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =
MTL_MATERIAL_TRANSACTIONS
select * from mtl_material_transactions where transaction_source_id =
MTL_TRANSACTION_ACCOUNTS
select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =
Stage 3: Invoicing details
AP_INVOICE_DISTRIBUTIONS_ALL
select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =
AP_INVOICES_ALL
select * from ap_invoices_all where invoice_id in(select invoice_id from ap_invoice_distributions_all where po_distribution_id in( select po_distribution_id from po_distributions_all where po_header_id =
Stage 4 : Many Time there is tie up with Project related PO
PA_EXPENDITURE_ITEMS_ALL
select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in( select to_char(transaction_id) from mtl_material_transactionswhere transaction_source_id =
Stage 5 : General Ledger
Prompt 17. GL_BC_PACKETS ..This is for encumbrances
SELECT * FROM gl_bc_packets WHERE reference2 IN (’
GL_INTERFACE
SELECT *FROM GL_INTERFACE GLIWHERE user_je_source_name =’Purchasing’AND gl_sl_link_table =’RSL’AND reference21=’PO’AND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLI.reference22 =RRSL.reference2AND GLI.reference23 =RRSL.reference3AND GLI.reference24 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactionswhere po_header_id
GL_IMPORT_REFERENCES
SELECT *FROM gl_import_references GLIRWHERE reference_1=’PO’AND gl_sl_link_table =’RSL’AND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3 =RRSL.reference3AND GLIR.reference_4 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactions where po_header_id =
SQL Loader Program
SQL Loader
SQL LOADER is an Oracle utility used to load data into table given a datafile which has the records that need to be loaded. SQL*Loader takes data file, as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files called a
log file, bad file or reject file, discard file.
- Log file tells you the state of the tables and indexes and the number of logical records already read from the input datafile. This information can be used to resume the load where it left off.
- Bad file or reject file gives you the records that were rejected because of formatting errors or because they caused Oracle errors.
- Discard file specifies the records that do not meet any of the loading criteria like when any of the WHEN clauses specified in the control file. These records differ from rejected records.
Structure of the data file:
The data file can be in fixed record format or variable record format.
Fixed Record Format would look like the below. In this case you give a specific position where the Control file can expect a data field:
7369 SMITH CLERK 7902 12/17/1980 800
7499 ALLEN SALESMAN 7698 2/20/1981 1600
7521 WARD SALESMAN 7698 2/22/1981 1250
7566 JONES MANAGER 7839 4/2/1981 2975
7654 MARTIN SALESMAN 7698 9/28/1981 1250
7698 BLAKE MANAGER 7839 5/1/1981 2850
7782 CLARK MANAGER 7839 6/9/1981 2450
7788 SCOTT ANALYST 7566 12/9/1982 3000
7839 KING PRESIDENT 11/17/1981 5000
7844 TURNER SALESMAN 7698 9/8/1981 1500
7876 ADAMS CLERK 7788 1/12/1983 1100
7900 JAMES CLERK 7698 12/3/1981 950
7902 FORD ANALYST 7566 12/3/1981 3000
7934 MILLER CLERK 7782 1/23/1982 1300
Variable Record Format would like below where the data fields are separated by a delimiter.
Note: The Delimiter can be anything you like. In this case it is "|"
1196700|9|0|692.64
1378901|2|3900|488.62
1418700|2|2320|467.92
1418702|14|8740|4056.36
1499100|1|0|3.68
1632800|3|0|1866.66
1632900|1|70|12.64
1637600|50|0|755.5
Structure of a Control file:
Sample CTL file for loading a Variable record data file:
OPTIONS (SKIP = 1) --The first row in the data file is skipped without loading
LOAD DATA
INFILE '$FILE' -- Specify the data file path and name
APPEND -- type of loading (INSERT, APPEND, REPLACE, TRUNCATE
INTO TABLE "APPS"."BUDGET" -- the table to be loaded into
FIELDS TERMINATED BY '|' -- Specify the delimiter if variable format datafile
OPTIONALLY ENCLOSED BY '"' --the values of the data fields may be enclosed in "
TRAILING NULLCOLS -- columns that are not present in the record treated as null
(ITEM_NUMBER "TRIM(:ITEM_NUMBER)", -- Can use all SQL functions on columns
QTY DECIMAL EXTERNAL,
REVENUE DECIMAL EXTERNAL,
EXT_COST DECIMAL EXTERNAL TERMINATED BY WHITESPACE "(TRIM(:EXT_COST))" ,
MONTH "to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD-MON-YY')" ,
DIVISION_CODE CONSTANT "AUD" -- Can specify constant value instead of
Getting value from datafile
)
OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.
SKIP = n -- Number of logical records to skip (Default 0)
LOAD = n -- Number of logical records to load (Default all)
ERRORS = n -- Number of errors to allow (Default 50)
ROWS = n -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)
BINDSIZE = n -- Size of conventional path bind array in bytes (System-dependent default)
SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} -- Suppress messages during run
(header, feedback, errors, discards, partitions, all)
DIRECT = {TRUE | FALSE} --Use direct path (Default FALSE)
PARALLEL = {TRUE | FALSE} -- Perform parallel load (Default FALSE)
LOAD DATA statement is required at the beginning of the control file.
INFILE: INFILE keyword is used to specify location of the datafile or datafiles.
INFILE * specifies that the data is found in the control file and not in an external file. INFILE '$FILE', can be used to send the filepath and filename as a parameter when registered as a concurrent program.INFILE '/home/vision/kap/import2.csv' specifies the filepath and the filename.
Example where datafile is an external file:
LOAD DATA
INFILE '/home/vision/kap/import2.csv'
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )
Example where datafile is in the Control file:
LOAD DATA
INFILE *
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )
BEGINDATA
7369,SMITH,7902,Accounting
7499,ALLEN,7698,Sales
7521,WARD,7698,Accounting
7566,JONES,7839,Sales
7654,MARTIN,7698,Accounting
Example where file name and path is sent as a parameter when registered as a concurrent program
LOAD DATA
INFILE '$FILE'
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )
TYPE OF LOADING:
INSERT -- If the table you are loading is empty, INSERT can be used.
APPEND -- If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded.
REPLACE -- All rows in the table are deleted and the new data is loaded
TRUNCATE -- SQL*Loader uses the SQL TRUNCATE command.
INTO TABLE is required to identify the table to be loaded into. In the above example INTO TABLE "APPS"."BUDGET", APPS refers to the Schema and BUDGET is the Table name.
FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc)
OPTIONALLY ENCLOSED BY '"' specifies that data fields may also be enclosed by quotation marks.
TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.
Loading a fixed format data file:
LOAD DATA
INFILE 'sample.dat'
INTO TABLE emp
( empno POSITION(01:04) INTEGER EXTERNAL,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL,
sal POSITION(32:39) DECIMAL EXTERNAL,
comm POSITION(41:48) DECIMAL EXTERNAL,
deptno POSITION(50:51) INTEGER EXTERNAL)
Steps to Run the SQL* LOADER from UNIX:At the prompt, invoke SQL*Loader as follows:
sqlldr USERID=scott/tiger CONTROL= LOG=
name>
SQL*Loader loads the tables, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.
Register as concurrent Program:
Place the Control file in $CUSTOM_TOP/bin.
Define the Executable. Give the Execution Method as SQL*LOADER.
Define the Program. Add the Parameter for FILENAME.
Skip columns:
You can skip columns using the 'FILLER' option.
Load Data
--
--
--
TRAILING NULLCOLS
(
name Filler,
Empno ,
sal
)
--
--
--
TRAILING NULLCOLS
(
name Filler,
Empno ,
sal
)
here the column name will be skipped.
Subscribe to:
Posts (Atom)