- We create order header only (using sales order form) with all the necessary information like customer, order type, price list, sales person, sales credits, warehouse and the other values as deemed necessary.
- Now we create line information in Excel worksheet (a template is created). We enter values for the fields like Item, quantity, price list name (if that is different from header), Line Type, Sales Person (different from header), any DFF segments, Shipping Instructions if any, warehouse name so on. We can have as many fields as that can fit in the order lines table. But instead of entering all the values, we use defaulting rules to default a lot of values from the header.
- In this worksheet there is a simple marco written that we use to generate another worksheet in the same file for the entered rows.This macro does some basic validations on the information entered in the worksheet and generates the worksheet with this statement for each row.
- insert into custom_lines_load_tbl (column1,column2,….) values ( field1,field2,……)
- Now using these rows with this statement (generated worksheet) we create a simple text file (copy and paste).
- As a next step we upload this file as an attachment to the order header that we have created in the step number 1.
- Now we run a custom program to create order lines into this order. This program takes order number as a parameter. This program reads the file contents line by line and executes the insert statements. This is the heart of the solution which is to read the contents of the file which is stored as LOB and executing the statements. After all the insert statements are executed successfully, the program calls oe_order_pub API to create order lines.
- As you can see the program uses this feature : reading the file which is stored in the database as LOB line by line. Here is the code snippet to read the attached file stored as a file.
PROCEDURE parse_and_load_file (
ERROR_CODE OUT VARCHAR2,
retcode OUT NUMBER,
p_order_number IN NUMBER
)
IS
fil BLOB;
pos INTEGER := 0;
amt BINARY_INTEGER := 32767;
buf RAW (2000);
l_start RAW (3000);
l_end RAW (3000);
l_start_pos INTEGER;
l_length INTEGER;
nth INTEGER := 1;
i INTEGER := 2;
l_text VARCHAR2 (4000);
l_end_pos INTEGER;
l_plsql_block VARCHAR2 (4000);
k INTEGER;
l_write_buff RAW (2000);
l_text1 VARCHAR2 (4000);
x_errbuf VARCHAR2 (4000);
x_retcode NUMBER;
BEGIN
BEGIN
begin
delete custom_lines_upload_tbl
where order_number=p_order_number
and line_Id is null; –this is to make sure we do not load duplicate data
exception when others then
null;
end;
SELECT file_data
INTO fil
FROM fnd_lobs
WHERE file_id =
(SELECT media_id
FROM fnd_documents_vl
WHERE document_id IN (
SELECT a.document_id
FROM fnd_attached_documents a, fnd_documents b
WHERE a.document_id = b.document_id
AND b.category_id =
(SELECT category_id
FROM fnd_document_categories_tl
WHERE user_name = ‘Custom Lines Upload’)
AND entity_name = ‘OE_ORDER_HEADERS’
AND pk1_value = (SELECT header_id
FROM oe_order_headers_all
WHERE order_number = p_order_number)))
FOR UPDATE;
EXCEPTION
WHEN OTHERS
THEN
debug_log
( ‘Order :’
|| p_order_number
|| ‘ does not have any file attached to it to load. Please attach the file and then try running this program’
);
END;
k := DBMS_LOB.ISOPEN (fil);
— debug_log(‘Checking whether the file is already open’);
IF k = 1
THEN
–debug_log(‘file is already open. Closing it’);
DBMS_LOB.CLOSE (fil);
END IF;
BEGIN
–debug_log(‘Opening the file’);
DBMS_LOB.OPEN (fil, DBMS_LOB.lob_readonly);
k := DBMS_LOB.ISOPEN (fil);
EXCEPTION
WHEN OTHERS
THEN
debug_log
(‘Exception occured in checking whether the file is open’);
END;
IF k = 1
THEN
LOOP
l_start := UTL_RAW.cast_to_raw (‘insert’);
l_end := UTL_RAW.cast_to_raw (‘;’);
l_start_pos := DBMS_LOB.INSTR (fil, l_start, 1, nth);
pos := DBMS_LOB.INSTR (fil, l_end, 1, nth);
buf := ”;
–debug_log(‘Start position is :’||l_start_pos);
–debug_log(‘End position is :’||pos);
l_end_pos := (pos + 1) – (l_start_pos);
–debug_log(‘Length of the string is :’||l_end_pos);
–debug_log(‘Reading the file’);
DBMS_LOB.READ (fil, l_end_pos, l_start_pos, buf);
l_text := UTL_RAW.cast_to_varchar2 (buf);
l_plsql_block :=
‘begin’
|| fnd_global.NEWLINE ()
|| l_text
|| fnd_global.NEWLINE ()
|| ‘commit;’
|| fnd_global.NEWLINE ()
|| ‘exception when others then’
|| fnd_global.NEWLINE ()
||
–’debug_log(”Exception occured in processing the line”);’||fnd_global.NewLine()||
‘NULL;’
|| fnd_global.NEWLINE ()
|| ‘end;’;
–debug_log(‘———————————————–’);
–debug_log(l_plsql_block);
–debug_log(‘———————————————–’);
BEGIN
–Now execute the dynamic pl/sql block to insert rows into custom table from the file
EXECUTE IMMEDIATE l_plsql_block;
EXCEPTION
WHEN OTHERS
THEN
–debug_log(‘Exception Occured’);
–debug_log(l_plsql_block);
–debug_log(‘Exception is :’||substrb(sqlerrm, 1, 55));
NULL;
END;
nth := nth + 1;
END LOOP;
DBMS_LOB.CLOSE (fil);
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
k := NULL;
k := DBMS_LOB.ISOPEN (fil);
IF k = 1
THEN
DBMS_LOB.CLOSE (fil);
END IF;
WHEN OTHERS
THEN
BEGIN
— debug_log(‘End of File reached in Exception. Closing file’);
k := NULL;
k := DBMS_LOB.ISOPEN (fil);
IF k = 1
THEN
DBMS_LOB.CLOSE (fil);
COMMIT;
END IF;
END;
–Here call the oe_order_pub API to create the lines passing the line table from this custom table.
END parse_and_load_file;
Records needs to be inserted into rcv_transactions_interface with processing_status_code and transaction_status_code as ‘PENDING’ and transaction_type of ‘RECEIVE’. and also inserted into rcv_shipment_headers which creates the shipment header.
Interface Tables: –
- rcv_headers_interface
- rcv_transactions_interface
- mtl_transaction_lots_interface
Error Table: –
- po_interface_errors
Base Tables:
- rcv_shipment_headers
- rcv_shipment_lines
- rcv_transactions
- mtl_lot_numbers
- mtl_material_transactions
- rcv_lot_transactions
R12 – Sample Procedure to Receive PO by inserting records into ROI
DECLARE
x_user_id NUMBER;
x_resp_id NUMBER;
x_appl_id NUMBER;
x_po_header_id NUMBER;
x_vendor_id NUMBER;
x_segment1 VARCHAR2 (20);
x_org_id NUMBER;
x_line_num NUMBER;
l_chr_lot_number VARCHAR2 (50);
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (50);
v_count NUMBER;
BEGIN
DBMS_OUTPUT.put_line (‘RCV Sample Insert Script Starts’);
DBMS_OUTPUT.put_line (‘**************************************’);
SELECT po_header_id, vendor_id, segment1, org_id
INTO x_po_header_id, x_vendor_id, x_segment1, x_org_id
FROM po_headers_all
WHERE segment1 = ‘380087’ — Enter The Po Number which needs to be received
AND org_id = 308 — Enter the org_id
AND approved_flag = ‘Y’
AND nvl(cancel_flag, ‘N’) = ‘N’;
SELECT DISTINCT
u.user_id,
to_char(a.responsibility_id) responsibility_id,
b.application_id
INTO
x_user_id, x_resp_id, x_appl_id
from
apps.fnd_user_resp_groups_direct a,
apps.fnd_responsibility_vl b,
apps.fnd_user u,
apps.fnd_application fa
where
a.user_id = u.user_id
and a.responsibility_id = b.responsibility_id
and a.responsibility_application_id = b.application_id
and sysdate between a.start_date and nvl(a.end_date,sysdate+1)
and fa.application_id (+) = b.application_id
and upper(u.user_name) = ‘A42485’ — Enter the User_name
and b.responsibility_name = ‘Inventory’; — Enter The Responsibility Name
DBMS_OUTPUT.put_line (‘Inserting the Record into Rcv_headers_interface’);
DBMS_OUTPUT.put_line (‘*********************************************’);
INSERT INTO rcv_headers_interface
(header_interface_id, GROUP_ID, processing_status_code,
receipt_source_code, transaction_type, last_update_date,
last_updated_by, last_update_login, creation_date, created_by,
vendor_id,expected_receipt_date, validation_flag)
SELECT rcv_headers_interface_s.NEXTVAL, rcv_interface_groups_s.NEXTVAL,
‘PENDING’, ‘VENDOR’, ‘NEW’, SYSDATE, x_user_id, 0,SYSDATE, x_user_id,
x_vendor_id, SYSDATE, ‘Y’
FROM DUAL;
DECLARE
CURSOR po_line
IS
SELECT
pl.org_Id, pl.po_header_id, pl.item_id, pl.po_line_id, pl.line_num, pll.quantity,
pl.unit_meas_lookup_code, mp.organization_code,
pll.line_location_id, pll.closed_code, pll.quantity_received,
pll.cancel_flag, pll.shipment_num,
pda.destination_type_code,
pda.deliver_to_person_id,
pda.deliver_to_location_id,
pda.destination_subinventory,
pda.destination_organization_id
FROM po_lines_all pl, po_line_locations_all pll,mtl_parameters mp, apps.po_distributions_all pda
WHERE pl.po_header_id = x_po_header_id
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = mp.organization_id;
BEGIN
FOR rec_det IN po_line LOOP
IF rec_det.closed_code IN (‘APPROVED’, ‘OPEN’)
AND rec_det.quantity_received <>
THEN
DBMS_OUTPUT.put_line (‘Inserting the Record into Rcv_Transactions_Interface’);
DBMS_OUTPUT.put_line (‘*********************************************’);
INSERT INTO rcv_transactions_interface
(interface_transaction_id, GROUP_ID,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login, transaction_type,
transaction_date, processing_status_code,
processing_mode_code, transaction_status_code,
po_header_id, po_line_id, item_id, quantity, unit_of_measure,
po_line_location_id, auto_transact_code,
receipt_source_code, to_organization_code,
source_document_code, document_num,
destination_type_code,deliver_to_person_id,
deliver_to_location_id,subinventory,
header_interface_id, validation_flag)
SELECT rcv_transactions_interface_s.NEXTVAL,
rcv_interface_groups_s.CURRVAL, SYSDATE, x_user_id,
SYSDATE, x_user_id, 0, ‘RECEIVE’, SYSDATE, ‘PENDING’,
‘BATCH’, ‘PENDING’, rec_det.po_header_id,rec_det.po_line_id,
rec_det.item_id, rec_det.quantity,
rec_det.unit_meas_lookup_code,
rec_det.line_location_id, ‘DELIVER’, ‘VENDOR’,
rec_det.organization_code, ‘PO’, x_segment1,
rec_det.destination_type_code, rec_det.deliver_to_person_id,
rec_det.deliver_to_location_id, rec_det.destination_subinventory,
rcv_headers_interface_s.CURRVAL, ‘Y’
FROM DUAL;
DBMS_OUTPUT.put_line (‘PO line:’ rec_det.line_num ‘ Shipment: ‘ rec_det.shipment_num ‘ has been inserted into ROI.’);
select count(*)
into v_count
from mtl_system_items
where inventory_item_id = rec_det.item_id
and lot_control_code = 2 — 2 – full_control, 1 – no control
and organization_id = rec_det.destination_organization_id;
IF v_count > 0 then
DBMS_OUTPUT.put_line (‘The Ordered Item is Lot Controlled’);
DBMS_OUTPUT.put_line (‘Generate the Lot Number for the Lot Controlled Item’);
BEGIN
— initialization required for R12
mo_global.set_policy_context (‘S’, rec_det.org_id);
mo_global.init (‘INV’);
— Initialization for Organization_id
inv_globals.set_org_id (rec_det.destination_organization_id);
— initialize environment
fnd_global.apps_initialize (user_id => x_user_id,
resp_id => x_resp_id,
resp_appl_id => x_appl_id);
DBMS_OUTPUT.put_line (‘Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers’);
DBMS_OUTPUT.put_line (‘*********************************************’);
l_chr_lot_number :=
inv_lot_api_pub.auto_gen_lot
(p_org_id => rec_det.destination_organization_id,
p_inventory_item_id => rec_det.item_id,
p_parent_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
p_api_version => 1.0,
p_init_msg_list => ‘F’,
p_commit => ‘T’,
p_validation_level => 100,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data);
IF l_chr_return_status = ‘S’ THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
DBMS_OUTPUT.put_line (‘Lot Number Created for the item is => ‘ l_chr_lot_number);
END;
DBMS_OUTPUT.put_line (‘Inserting the Record into mtl_transaction_lots_interface ‘);
DBMS_OUTPUT.put_line (‘*********************************************’);
INSERT INTO mtl_transaction_lots_interface
( transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
lot_number,
transaction_quantity,
primary_quantity,
serial_transaction_temp_id,
product_code,
product_transaction_id)
(select
mtl_material_transactions_s.nextval,–transaction_interface_id
sysdate, –last_update_date
x_user_id, –last_updated_by
sysdate, –creation_date
x_user_id, –created_by
-1, –last_update_login
l_chr_lot_number, –lot_number
rec_det.quantity, –transaction_quantity
rec_det.quantity, –primary_quantity
NULL, –serial_transaction_temp_id
‘RCV’, –product_code
rcv_transactions_interface_s.currval –product_transaction_id
from dual);
ELSE
DBMS_OUTPUT.put_line (‘The Ordered Item is Not Lot Controlled’);
DBMS_OUTPUT.put_line (‘********************************************’);
END IF;
ELSE
DBMS_OUTPUT.put_line ( ‘PO line ‘ rec_det.line_num’-‘ rec_det.shipment_num ‘ is either closed, cancelled, received.’);
DBMS_OUTPUT.put_line (‘*********************************************’);
END IF;
END LOOP;
DBMS_OUTPUT.put_line (‘RCV Sample Insert Script Ends’);
DBMS_OUTPUT.put_line (‘*****************************************’);
END;
COMMIT;
END;
— Cross Check the Records in the Interface Table
select * from apps.rcv_headers_interface
where created_by = 2083
and group_id = ***
select *
from apps.rcv_transactions_interface
where created_by = 2083
and group_id = ***
select * from apps.mtl_transaction_lots_interface
where created_by = 2083
and lot_number = ***
and product_transaction_id in
(select interface_transaction_id from apps.rcv_transactions_interface
where created_by = 2083 and group_id = ***)
— Check for the Error
select * from po_interface_errors
where batch_id = ***
— Reprocessing the records from the interface if the same errored out there.
UPDATE rcv_headers_interface
SET processing_request_id = NULL,
validation_flag = ‘Y’,
processing_status_code = ‘PENDING’
WHERE GROUP_ID = ***
UPDATE rcv_transactions_interface
SET request_id = NULL,
processing_request_id = NULL,
validation_flag = ‘Y’,
processing_status_code = ‘PENDING’,
transaction_status_code = ‘PENDING’,
processing_mode_code = ‘BATCH’
WHERE interface_transaction_id = ***
AND batch_id = ***
— Verification of the base tables Once the Receiving Transactions Processor is Completed
select * from apps.rcv_shipment_headers
where created_by = 2083
select * from apps.rcv_shipment_lines
where created_by = 2083
and po_header_id = 619
select * from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083
select * from apps.mtl_lot_numbers
where lot_number in (‘A6631684’, ‘A6631685’, ‘A6631686’)
select * from apps.rcv_lot_transactions
where lot_num in (‘A6631684’, ‘A6631685’, ‘A6631686’)
select * from apps.mtl_material_transactions
where created_by = 2083
and rcv_transaction_id in (select transaction_id from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083)
SELECT (SELECT segment1
FROM po_headers_all
WHERE po_header_id = pl.po_header_id
AND org_id = pl.org_id) po_number, pl.po_header_id,
pl.item_id, pl.po_line_id, pl.line_num, pll.shipment_num,
pll.quantity, pl.unit_meas_lookup_code, mp.organization_code, pll.line_location_id,
pll.closed_code, pll.quantity_received, pll.cancel_flag,
pll.shipment_num, pda.destination_type_code, pda.deliver_to_person_id,
pda.deliver_to_location_id, pda.destination_subinventory
FROM
apps.po_lines_all pl,
apps.po_line_locations_all pll,
apps.mtl_parameters mp,
apps.po_distributions_all pda
WHERE 1 = 1
AND pl.po_header_id = 619
AND pl.org_id = 308
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = mp.organization_id
order by 1, 5, 6
select process_code from po_headers_interface;
select process_code from po_lines_interface;
please do the following:
Run the program – Purchasing Interface Errors Report
choose parameter : PO_DOCS_OPEN_INTERFACE
The report will list all the errors you have during importing. You can fix the data, then reset process_code = Null in both interface tables, rerun the Purchasing Document Open Interface.
update po_headers_interface set process_code = null
where process_code = ‘REJECTED’;
update po_lines_interface set process_code = null
where process_code = ‘REJECTED’;
- This table replaces the old PO_VENDORS table.
- It stores information about your supplier level attributes.
- Each row includes the purchasing, receiving, invoice, tax, classification, and general information.
- Oracle Purchasing uses this information to determine active suppliers.
- The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.
AP_SUPPLIER_SITES_ALL:
- This table replaces the old PO_VENDOR_SITES_ALL table.
- It stores information about your supplier site level attributes.
- There is a row for unique combination of supplier address, operating unit and the business relationship that you have with the supplier.
- The supplier address information is not maintained in this table and is maintained in TCA. The reference to the internal identifier of address in TCA will be stored in AP_SUPPLIER_SITES_ALL.LOCATION_ID, to link the address record in TCA.
- Each row includes the supplier reference, purchasing, invoice, and general information.
AP_INVOICES_ALL:
- It contains records for invoices you enter.
- There is one row for each invoice you enter.
- An invoice can have one or more invoice distribution lines and can have one or more scheduled payments.
AP_INVOICE_LINES_ALL:
- It contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
- An invoice can have one or more invoice lines.
- An invoice line represents goods (direct or indirect materials), service(s), and/or associated tax/freight/miscellaneous charges invoiced from a supplier.
- An invoice line should contain all the attributes that are present on the physical or electronic invoice presented by the supplier.
AP_INVOICE_DISTRIBUTIONS_ALL:
- It holds the distribution information that is manually entered or system-generated.
- There is one row for each invoice distribution and a distribution must be associated with an invoice.
- An invoice can have multiple distributions.
AP_INVOICE_PAYMENTS_ALL:
- It contains records of invoice payments that you made to suppliers.
- There is one row for each payment you make for each invoice and there is one payment and one invoice for each payment in this table.
- Oracle Payables application updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick payment.
- When you void a payment, your Oracle Payables inserts an additional payment line that is the negative of the original payment line.
AP_PAYMENT_SCHEDULES_ALL:
- This table stores information about scheduled payment information on invoices.
AP_PAYMENT_HISTORY_ALL:
- It stores the clearing/unclearing history for payments.
- It also stores the maturity history for future dated payments.
- The table contains a row for each future dated payment, once the future dated payment matures, i.e. becomes negotiable.
- Any time a payment is cleared or uncleared, a row is inserted into this table for the payment.
AP_BATCHES_ALL:
- It contains summary information about invoices you enter in batches if you enable the Batch Control Payables option.
- There is one row for each batch of invoices you enter.
- If you enable Batch Control, each invoice must correspond to a record in this table.
- Your Oracle Payables application uses this information to group together invoices that one person entered in a batch.
AP_CHECKS_ALL:
- It stores information about payments issued to suppliers or refunds received from suppliers.
- There is one row for each payment you issue to a supplier or refund received from a supplier.
- Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers.
- Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. Oracle Payables application also stores address information for all payments.
AP_HOLDS_ALL:
- It contains information about holds that you or your Oracle Payables application place on an invoice.
- For non-matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice-shipment match.
- An invoice may have one or more corresponding rows in this table.
- Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table.
AP_BANK_ACCOUNTS_ALL:
- It contains information about your bank accounts.
- There is one row for each bank account you define and each bank account must be affiliated with one bank branch.
AP_BANK_ACCOUNT_USES_ALL:
- It stores information for the internal and external bank accounts you define in Oracle Payables and Oracle Receivables applications.
AP_CARDS_ALL:
- It stores information about the corporate credit cards issued to your employees by your corporate credit card providers.
AP_TRIAL_BALANCE:
- It contains denormalized information about invoices and payments posted to the accrual set of books.
Latest Posts
- R12 – How to Handle NULL for :$FLEX$.VALUE_SET_NAME In Oracle ERPAugust 25, 2023 - 1:20 pm
- R12 – How to Delete Oracle AR TransactionsMarch 22, 2019 - 8:37 pm
- How to Define Custom Key Flexfield (KFF) in R12January 19, 2018 - 5:43 pm
- AutoLock Box Concepts In R12November 10, 2017 - 8:30 am
- R12 – java.sql.SQLException: Invalid column type in OAFSeptember 15, 2017 - 9:39 am
S | M | T | W | T | F | S |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
Recent Comments