OTC

Order cycle
1.customer sends details of order or sales dept brings order from customer.
2.Enter the order in Sales order (SO)
3.Book an SO
4.Check for ATP (Available to promise)
5. Check for CTP (Capable to Promise) and CTD (capable to deliver)
6.Schedule SOship date
7.Send SO Acknowledgement
8.Resolve SO Holds
9.Release Credit Holds (Go to SO actions tab and release hold or increase the credit amount in customers->>standard-->>profile:amount)
10.Pick material (Pick release material from Inventory)
11.Pick wave MO is generated by system and material is moved to staging
12.prepare material for shipment (Packing)
13.Load Material
14.Ship material (Send ship documentslike bill of lading,customer invoice,packing list,vehicle load sheet)
15.Generate invoice in AR AR-> setup-> Auto invoice and send it to cus.
16.customer followup (calling customer thruphone,Dunning letters etc)
17.Customer payment aganist the invoice and receive payment
18.Financial reconcilation (reconsile shipment and payment done by customer)
Order to cash life cycle contains following steps
Order Entry --> OrderBooked --> Pick release --> Ship confirm --> Auto invoice
--> invoice --> Receipt --> Bank Reconcilations

Steps in Order management
Enter the sales orderBook the sales order
Steps in Shipping exexution
release the sales order for picking
Ship confirm the sales order
Steps in Receivables
Run auto invoiceInvoiceBank Reconcilation

Technical point of o2c(order to cash) is as follows
first we setup the customer,tax category details,item details,pricing definitions,payment terms,freight terms and then only we can order the product.
for set up main base tables are
RA_CUSTOMERS,
RA_ADDRESSES,
RA_CUST_SITE_USES_ALL
then go for the order entry
here effected tables are
OE_ORDER_HEADERS_ALL,
OE_ORDER_LINES_ALL
flow_status_code for both tables will be 'ENTER'
then book the order
flow_status_code for both tables of
OE_ORDER_HEADERS_ALL,
OE_ORDER_LINES_ALLwill be 'Booked'
WSH_DELIVERY_DETAILS,
WSH_DELIVERY_ASSIGNMENTS will be going to effect.
the pick release
the flow_status_code for OE_ORDER_HEADERS_ALL will be 'BOOKED"
and flow_status_code for OE_ORDER_LINES_ALL will be 'awaiting shipping"
pick conform
flow_status_code for both tables of OE_ORDER_HEADERS_ALL,
OE_ORDER_LINES_ALL will be 'PICKED'
ship conform
flow_status_code for both tables of OE_ORDER_HEADERS_ALL,
OE_ORDER_LINES_ALL will be 'SHIPPED'
here in WSH_NEW_ASSIGNMENTS the status_code is 'CL' then the order details are conformed and otherwise it is 'Open' stage
here in WSH_DELIVERY_DETAILS the oe_interface_flag is 'Y' then the order is closed and if it is 'N' it is an error in the order line details and we need to assign line level order details
once the shipping is conformed invoice will be generated
the effected tables are RA_CUSTOMER_TRX_ALL,RA_CUSTOMER_TRX_LINES_ALL
this is O2C

API's

API's
FND_PROGRAM.EXECUTABLE ( )
FND_PROGRAM.DELETE_EXECUTABLE( )
FND_PROGRAM.REGISTER( )
FND_PROGRAM.DELETE_PROGRAM( )
FND_PROGRAM.PARAMETER( )
FND_PROGRAM.DELETE_PARAMETER( )
FND_PROGRAM.INCOMPATIBILITY( )
FND_PROGRAM.DELETE_INCOMPATIBILITY( )
FND_PROGRAM.REQUEST_GROUP( )
FND_PROGRAM.DELETE_GROUP( )
FND_PROGRAM.ADD_TO_GROUP( )
FND_PROGRAM.REMOVE_FROM_GROUP( )
FND_REQUEST.SUBMIT_REQUEST( )
FND_CONCURRENT.WAIT_FOR_REQUEST( )
FND_REQUEST.SET_PRINT_OPTIONS ( )
FND_GLOBAL.USER_IDFND_GLOBAL.APPS_INITIALIZE(user_id in number,resp_id in number,resp_appl_id in number);
FND_GLOBAL.LOGIN_ID
FND_GLOBAL.CONC_LOGIN_ID
FND_GLOBAL.PROG_APPL_ID
FND_GLOBAL.CONC_PROGRAM_ID
FND_GLOBAL.CONC_REQUEST_ID
FND_PROFILE.PUT(name,value)
FND_PROFILE.GET(name IN varchar2,value out varchar2)
API’S IN APPS
PO
PO_CUSTOM_PRICE_PUB
PO_DOCUMENT_CONTROL_PUB
PO_DOC_MANAGER_PUB
PO_WFDS_PUB
AP
AP_NOTES_PUB
AP_WEB_AUDIT_LIST_PUB
INV
INV_COST_GROUP_PUB
INV_ITEM_CATALOG_ELEM_PUB
INV_ITEM_CATEGORY_PUB
INV_ITEM_PUB
INV_ITEM_REVISION_PUB
INV_ITEM_STATUS_PUB
INV_LOT_API_PUB
INV_MATERIAL_STATUS_PUB
INV_MOVEMENT_STATISTICS_PUB
INV_MOVE_ORDER_PUB
INV_PICK_RELEASE_PUB
INV_PICK_WAVE_PICK_CONFIRM_PUB
INV_RESERVATION_PUB
INV_SERIAL_NUMBER_PUB
INV_SHIPPING_TRANSACTION_PUB

Concurrent Program Submission Through Backend

declare
l_request_id number(9) ;
begin
l_request_id := Fnd_Request.submit_request
( 'PO', -Concurrent Prog Application Name
'SQL-USERS', -Conccurrent Prog Short Name
'Users Data', -Concurrent Prog description '',
-start time '',
-sub request 1000,
-first parameter value 2000,
-second parameter value
'CREATION_DATE');-third parameter value
Commit;
if l_request_id = 0
then fnd_file.put_line(fnd_file.log,'Prograqm not sumitted Succesfully');
else
fnd_file.put_line(fnd_file.log,'Prograqm sumitted Succesfully Request ID ='l_request_id);
End If;
Exception
when others then
fnd_file.put_line(fnd_file.log,'Error occured during Porgram submission');
End ;

from Triggers
declare
l_request_id number(9) ;
begin
l_request_id := Fnd_Request.submit_request
( 'PO', -Concurrent Prog ApplciationName
'SQL-USERS', -Conccurrent Prog Short Name
'Users Data', -Concurrent Prog description '',
-start time '',
-sub request 1000,
-first parameter value 2000,
-second parameter value
'CREATION_DATE','','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','');
Commit;
if l_request_id = 0 then
fnd_file.put_line(fnd_file.log,'Prograqm not sumitted Succesfully');
else
fnd_file.put_line(fnd_file.log,'Prograqm sumitted Succesfully Request ID ='l_request_id);
End If;
Exception
when others then
fnd_file.put_line(fnd_file.log,'Error occured during Porgram submission');
End ;
fnd_global.apps_initialize(user_id, Resp_id, Resp_appl_id);
-To initialize the Application Environment by specifying the UserID and RespID system will verify the User Access details based on that it will submit the Program.
1)fnd_Program.executable
2)fnd_program.register
3)fnd_program.request_group
4)fnd_program.add_to_group - Add concurrent Pogram to the Group
5)fnd_program.parameter - To create parameters for concurrent Program
6)fnd_program.incompatibility - TO attach Incompatibility Programs List
7)fnd_program.delete_group - To delete the Request Group
Any Table ,Procedure,Package,view any database Object starting with "FND" then it is relatedfor AOL(Application Obejct Library) , AOL ObjectSchema Name :APPLSYS"fnd" is nothing but foundation

Requisition Interface

Requisition Interface - PO_REQUISITIONS_INTERFACE_ALL
-- insert data into Interface tables
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(interface_source_code
,source_type_code
,requisition_type
,destination_type_code
,item_id
,item_description
,quantity
,authorization_status
,preparer_id
,autosource_flag
,req_number_segment1 *** see the note
,header_attribute13 ---xtra infomation
,line_attribute15 ---xtra infomation
,uom_code
,destination_organization_id
,destination_subinventory
,deliver_to_location_id
,deliver_to_requestor_id
,need_by_date
,gl_date
,charge_account_id
,accrual_account_id
,variance_account_id
,org_id
,suggested_vendor_id
,suggested_vendor_site_id
,unit_price
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES ('INV'
,'VENDOR'
,'PURCHASE'
,'INVENTORY'
,rec_get_lines_info.inventory_item_id
,rec_get_lines_info.item_desc
,rec_get_lines_info.ordered_quantity
,l_authorization_status --------'INCOMPLETE' or 'APPROVED'
,g_employee_id
,'P'
,l_req_segment1
,'ZZ' ---xtra infomation
,rec_get_lines_info.ship_to_org_id ---xtra infomation
,rec_get_lines_info.uom_code
,rec_get_lines_info.ship_from_org_id
,rec_get_lines_info.subinventory
,rec_get_lines_info.location_id
,get_requestor (fnd_global.user_id) --rec_get_lines_info.requestor
,rec_get_lines_info.schedule_ship_date
,SYSDATE
,rec_get_lines_info.charge_account
,rec_get_lines_info.ap_accrual_account
,rec_get_lines_info.invoice_price_var_account
,g_org_id
, rec_get_lines_info.vendor_id
, rec_get_lines_info.vendor_site_id
,rec_get_lines_info.list_price
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
);


Note:

Standard grouping rules provided by Oracle are by
Buyer
Category
Item
Location
Vendor or
ALL , these grouping rules can be over written by populating "req_number_segment1" .
When we use req_number_segment1 with ALL grouping option, requisiton will be grouped by req_number_segment1

Concurrent Program:

apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'REQIMPORT' --Program,
argument1 => 'INV' --Interface Source code,
argument2 => '' --Batch ID,
argument3 => 'ALL'--Group By,
argument4 => ''--Last Req Number,
argument5 => ''--Multi Distributions,
argument6 => 'N' --Initiate Approval after ReqImport
);

Purchase Order Interface

Purchase Order Interface -- PO_HEADERS_INTERFACE
Interface Tables Used

1)PO_HEADERS_INTERFACE
2)PO_LINES_INTERFACE
3)PO_DISTRIBUTIONS_INTERFACE

INSERT INTO po_headers_interface
(interface_header_id
,batch_id
,action
,org_id
,document_type_code
,vendor_id
,vendor_site_code
,vendor_site_id
,vendor_doc_num
,currency_code
,agent_id
,terms_id
,approval_status
,ship_to_location_id
,effective_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,attribute_category
,attribute9 --xtra information
,comments
)
VALUES (po_headers_interface_s.NEXTVAL
,po_headers_interface_s.CURRVAL
,'ORIGINAL'
,g_org_id
,'STANDARD'
,rec_get_header_info.vendor_id
,rec_get_header_info.vendor_site_code
,rec_get_header_info.vendor_site_id
,po_headers_interface_s.CURRVAL
,'USD'
,l_agent_id
,rec_get_header_info.terms_id
,'APPROVED'
,rec_get_header_info.deliver_to_location_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'DS Fields'
,rec_get_header_info.attribute9 --xtra information
,l_comments
)

INSERT INTO po_lines_interface
(interface_header_id
,interface_line_id
,requisition_line_id
,line_num
,shipment_num
,line_type_id
,item
,item_description
,category_id
,unit_of_measure
,quantity
,unit_price
,ship_to_organization_id
,line_location_id
,effective_date
,need_by_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,taxable_flag
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.NEXTVAL
,rec_get_line_info.requisition_line_id
,l_line_num
,1
,rec_get_line_info.line_type_id
,rec_get_line_info.segment1
,rec_get_line_info.item_description
,rec_get_line_info.category_id
,rec_get_line_info.primary_unit_of_measure
,rec_get_line_info.quantity
,rec_get_line_info.unit_price
,rec_get_line_info.destination_organization_id
, po_line_locations_s.NEXTVAL
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (rec_get_line_info.need_by_date, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'Y'
);


INSERT INTO po.po_distributions_interface
(interface_header_id
,interface_line_id
,interface_distribution_id
,req_distribution_id
,org_id
,distribution_num
,quantity_ordered
,destination_organization_id
,destination_type_code
,destination_subinventory
, deliver_to_location_id
,set_of_books_id
,charge_account_id
,budget_account_id
,accrual_account_id
,variance_account_id
,deliver_to_person_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.CURRVAL
,po.po_distributions_interface_s.NEXTVAL
,rec_get_distrib_info.distribution_id
,g_org_id
,rec_get_distrib_info.distribution_num
,rec_get_distrib_info.req_line_quantity
,rec_get_distrib_info.destination_organization_id
,rec_get_distrib_info.destination_type_code
,l_destination_subinventory
, rec_get_line_info.deliver_to_location_id
,rec_get_distrib_info.set_of_books_id
,rec_get_distrib_info.code_combination_id
,rec_get_distrib_info.budget_account_id
,l_new_accrual_account_id
,rec_get_distrib_info.variance_account_id
,rec_get_line_info.to_person_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
);

Concurrent Program:
apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'POXPOPDOI'--Program,
argument1 => ''--Buyer ID,
argument2 => 'STANDARD'--Document Type,
argument3 => ''--Document Subtype,
argument4 => 'N'--Process Items Flag,
argument5 => 'N'--Create Sourcing rule,
argument6 => ''--Approval Status,
argument7 => ''--Release Generation Method,
argument8 => ''--NULL,
argument9 => g_org_id--Operating Unit ID,
argument10 => ''--Global Agreement
);

Calling PO Approval workflow to approve POs

SELECT TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_wf_seq
FROM SYS.DUAL;

v_itemkey := TO_CHAR (l_header.po_header_id)
|| '-'
|| v_wf_seq;
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV'
,itemkey => v_itemkey
,workflowprocess => 'POAPPRV_TOP'
,actionoriginatedfrom => 'PO_FORM'
,documentid => l_header.po_header_id
,documentnumber => l_header.segment1
,preparerid => l_header.agent_id
,documenttypecode => 'PO'
,documentsubtype => 'STANDARD'
,submitteraction => 'APPROVE'
,forwardtoid => NULL
,forwardfromid => l_header.agent_id
,defaultapprovalpathid => 10
,note => NULL
,printflag => 'N'
,faxflag => NULL
,faxnumber => NULL
);

Concatenating Multiple Rows into single row

Concatenating Multiple Rows into single row
SELECT customer_product_id,
SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH(incident_number, '/') ,
'/',' ,')),3) Concatenated_String
FROM (SELECT cia.incident_number,cia.customer_product_id
,ROW_NUMBER () OVER (PARTITION BY customer_product_id ORDER BY customer_product_id) row#
FROM cs_incidents_all cia
,csi_item_instances cii
WHERE 1 = 1
AND cia.customer_product_id = cii.instance_id
AND cii.serial_number = 'XXXXXX'
AND NOT EXISTS (
SELECT 1
FROM cs_incident_statuses
WHERE NAME IN ('Cancelled', 'Closed')
AND incident_subtype = 'INC'
AND incident_status_id = cia.incident_status_id))
START
WITH ROW#=1
CONNECT
BY PRIOR row# = row#-1 and prior customer_product_id = customer_product_id
GROUP
BY customer_product_id

Spliting One row into Multi rows

Spliting One row into Multi rows
select * from (
with t as (select 'A-INSTALL,6-FOLLOWUP' str from dual)
SELECT trim(REGEXP_SUBSTR (str,'[^,]+' ,1,level))
FROM t
connect by instr(str, ',', 1, level - 1) > 0
and connect_by_root str = str)

SQL Loader Case Studies

SQL Loader Case Studies
http://www.cs.umbc.edu/portal/help/oracle8/server.815/a67792/ch04.htm

Importing material cost associated to item.

Importing material cost associated to item.


First check which cost elements are defined for the organizaion.


select cost_type_id,cost_type,description
from CST_COST_TYPES;


Output looks like this.


cost_type_id cost_type description
------------ ------------ ------------------------------------
1 Frozen Frozen Standard Cost Type
2 Average Average Cost Type
3 Pending Pending Standard Cost Type
.....


To find the sub element name defined for your organization for a particular cost type please check bom resources table.


In our example we are checking for material sub element in organization M1 with organization_id = 207 for cost_code type 1.


select resource_code,description,cost_element_id,cost_code_type
from bom_resources where organization_id = 207
and cost_code_type= 1;


resource_code description cost_element_id cost_code_type
------------- ----------------- --------------- --------------
Material Material Sub element 1 1
Labor Labor Sub element 1 1
Expense Expense Sub element 1 1


In our scenario we want to create a new item in organization M1 along with its material cost (say $11) assuming the same item is already created in master organization V1.


insert into mtl_system_items_interface
(process_flag, set_process_id, transaction_type,organization_id,segment1,
description,template_id,MATERIAL_COST,MATERIAL_SUB_ELEM)
values
(1,1,'CREATE',207,'TESTCOSTMATERIAL', 'Testing Item Material Cost Import',
259,11,'Material');
commit;


Note : Using mtl_system_items_interface 'Frozen cost' also be converted


Similary we can use MATERIAL_SUB_ELEM_ID, MATERIAL_OH_RATE,MATERIAL_OH_SUB_ELEM and MATERIAL_OH_SUB_ELEM_ID colums.


To check the subelements you can go to Cost management responsibility for your organization and under Setup > Sub-Elements you will see the respective
subelements being defined.


Now run the item import program ie. Item > Import > Import Items.


In Process Set enter 1 as we had used 1 in the set_process_id column.


Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.


Now go to organization items and query on 'TESTCOSTMATERIAL' the item that we had populated and you should be able to see the item.


Go to Tools > Item Costs and then hit Open button from Item Cost Summary screen
and you should see the details. In out example we see a material cost of $11 for the item in cost type 'Frozen'(Standard Costing).


NOTE: Item import cannot be used to update item costs.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You have to populate
CST_ITEM_CST_DTLS_INTERFACE,
CST_RESOURCE_COSTS_INTERFACE,
CST_RES_OVERHEADS_INTERFACE and
CST_DEPT_OVERHEADS_INTERFACE accordingly.


The concurrent request can be launched by navigating to cost->cost massedits->Import Cost Information.


The parameters for the Cost Import concurrent program and their description is
as follows :


Import Cost Option : A LOV is provided from which the user can select one of the import options which may be either to import Only item costs , Only resource costs , Only overhead rates or all the cost information .


Option Table from which data is processed
---------- --------------------------------------------
Only item cost cst_item_cst_dtls_interface
Only resource costs cst_resource_costs_interface
Only overhead rates cst_res_overheads_interface ,
cst_dept_overheads_interface


All Cost Information From all the four interface tables


Mode to run this request : A LOV is provided with possible two values , 'Insert new cost' or 'Remove and replace cost'.


The 'Insert new cost' mode , is useful if you are importing large # of items and are not sure if that Item/Organization/Cost Type combination already exists in the production tables, if it does then the row in the interface table would be flaged as errored and not imported.This would prevent any accidental overwrite of already existing data.
With 'Remove and replace cost' mode all the previous cost information for this item, cost_type and organization combination will be deleted from the production tables and the new information will overwrite (replace) the already existing one.


Group Id Option : A LOV is provided from which the user can either select 'ALL' or 'Specific Group Id' . If the user wishes to submit multiple Cost Import process requests he can do so by submitting one request per group id. For doing so the data in the interface tables should be stamped with distinct group id value by using the NEXTVAL from the sequence generator CST_LISTS_S .The use of this sequence geneartor is a MUST for generating multiple groups or may lead to data corruption as these interface tables are used by other processes too.
If the user selects "ALL" from the list then a group ID generated by a sequence will replace the group ID in the interface tables (if any) and all the unprocessed rows from the four interface table (viz.cst_item_cst_dtls_interface , cst_resource_costs_interface ,cst_res_overheads_interface , cst_dept_overheads_interface ) will be processed in one run.


Cost type to import to : The user is provided with a LOV from which he needs to select the cost type in which he wishes to import the cost information. Even if the user has populated a cost type or cost type ID in the interface tables, it would be overwritten with the one that is selected here.


The cost types that the user can pick from is restricted to the multi-org,
updateable cost types.


Delete succesfull rows : This parameter decides whether the successfully processed rows should be deleted from the interface tables at the end of the run. If the user selects 'Yes' then all the successful rows be deleted, basically rows that do not have their error flag set to "E".


Importing directly into Frozen/Average cost type (i.e non updateable cost types)and merging of new cost with existing costs is not supported at this time and would still have to be processed by cost update routines. Also when importing the costs from the interface table ,material overhead defaults (if any) specified for an Organization/Category would not be respected.




Minimum columns in each table that the user needs to provide
1. CST_ITEM_CST_DTLS_INTERFACE
The columns that the user has to provide are
a. Inventory_item_id
b. organization_ID or organization_code.
c. resource_ID or resource_code for cost elements other than 1(material).
If we are importing cost into material cost element and default material
subelement has been specified on 'Define Organization parameters' form
then that would be respected unless the user overides it with a value
in this column.
d. usage_rate_or_amount
e. cost_element_ID or cost_element
f. Process Flag (must be set to 1)


We default values for based_on_rollup, shrinkage_rate, inventory_asset_flag,
lot_size from the row from CST_ITEM_COSTS for this item and the default cost
type(default cost type of the cost type specified to import the costs into). If
there is no such row already defined in CST_ITEM_COSTS, then, the values for
these 4 columns also need to be specified and they have to be the same for all
rows of this item, cost type and organization combination.


CST_RESOURCE_COSTS_INTERFACE
a. Resource_ID or resource_code
b. organization_ID or organization_code
c. resource_rate
d. Process_flag (must be set to 1)


CST_RES_OVERHEADS_INTERFACE
a. Resource_ID or resource_code
b. Overhead_ID or overhead
c. Organization_ID or organization_code
d. Process_flag(must be set to 1)


CST_DEPT_OVERHEADS_INTERFACE
a. Department_ID or department
b. Overhead_ID or overhead
c. Organization_ID
d. Rate_or_amount
e. Process_flag(must be set to 1)


The other columns will be defaulted.


CST_ITEM_COSTS_INTERFACE can be used to import all the costs
MATERIAL_COST, OUTSIDE PROCESSING COST, OVER HEAD COST and other costs

Article from Uday Somavarapu Blog

Ship To address and Bill to Address Qry

Ship To address and Bill to Address Qry

SELECT hp.party_name
FROM hz_parties hp
,hz_party_sites site
,hz_locations loc
,hz_party_site_uses uses
,fnd_territories_vl terr
,hz_cust_site_uses hcsua
,hz_cust_acct_sites hcasa
WHERE site.party_id = hp.party_id
AND site.location_id = loc.location_id
AND site.party_site_id = uses.party_site_id(+)
AND loc.country = terr.territory_code
AND hp.status = 'A'
AND site.status = 'A'
AND hcsua.status = 'A'
AND uses.site_use_type = 'SHIP_TO'
AND NVL (uses.status, 'A') = 'A'
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = site.party_site_id
AND hcsua.site_use_code = uses.site_use_type
AND hcsua.site_use_id = ol.ship_to_org_id

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT hp.party_name
FROM hz_parties hp
,hz_party_sites site
,hz_locations loc
,hz_party_site_uses uses
,fnd_territories_vl terr
,hz_cust_site_uses hcsua
,hz_cust_acct_sites hcasa
WHERE site.party_id = hp.party_id
AND site.location_id = loc.location_id
AND site.party_site_id = uses.party_site_id(+)
AND loc.country = terr.territory_code
AND hp.status = 'A'
AND site.status = 'A'
AND hcsua.status = 'A'
AND uses.site_use_type = 'BILL_TO'
AND NVL (uses.status, 'A') = 'A'
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = site.party_site_id
AND hcsua.site_use_code = uses.site_use_type
AND hcsua.site_use_id = ol.invoice_to_org_id

OTC (Order To Cash Techinical Overview )

Order To Cash Techinical Overview

Enter order --> Book Order --> Pick Release --> Pick Slip --> Confirm Shipment àGenerate Invoice --> Customer follow up --> Receipt --> [Final Reconciliation]


Steps in Cycle:


1. Order Entry

This is first stage, when the order is entered in the system; it creates a record in order headers and Order Lines table.


Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table OE_ORDER_HEADERS_ALL
FLOW_STATUS_CODE = ENTERED,

BOOKED_FLAG = N),

Primary key=HEADER_ID

No record exists in any other table for this order till now.


Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID.

OE_ORDER_LINES_ALL

FLOW_STATUS_CODE = ENTERED,

BOOKED_FLAG = N,

OPEN_FLAG = Y

Primary key= LINE_ID

2. Order Booking

This is next stage, when Order is booked then the Flow status changed from Entered to Booked.At this stage, these below table get affected.


OE_ORDER_HEADERS_ALL (FLOW_STATUS_CODE as BOOKED, BOOKED_FLAG updated to Y)
OE_ORDER_LINES_ALL (FLOW_STATUS_CODE as AWAITING_SHIPPING, BOOKED_FLAGupdated Y)
WSH_DELIVERY_DETAILS (DELIVERY_DETAIL_ID is assigned here, RELEASED_STATUS ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)
WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in WSH_DELIVERY_DETAILS, DELIVERY_ID remains blank till this stage)

In shipping transaction form order status remains "Ready to Release".

At the same time, Demand interface program runs in background And insert into inventory tables MTL_DEMAND, here LINE_ID come as a reference in DEMAND_SOURCE_LINE

3. Reservation

This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once these programs get successfully get completed.


MTL_DEMAND andMTL_RESERVATIONS table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.

4. Pick Release

Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.

Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.
Once pick release is done these are the tables get affected:

· If step 3 is not done then MTL_RESERVATIONS gets updated now.

· WSH_NEW_DELIVERIES (one record gets inserted with SOURCE_HEADER_ID= order header ID, STATUS_CODE=OP =>open)

· WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ID gets assigned which comes from WSH_NEW_DELIVERIES)

· WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘S’ ‘submitted for release’)

· MTL_TXN_REQUEST_HEADERS

· MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)

· (move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)

· MTL_MATERIAL_TRANSACTIONS_TEMP (link to above tables through MOVE_ORDER_HEADER_ID/LINE_ID, this table holds the record temporally)

· MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)

· MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )

*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS


5. Pick Confirm/ Move Order Transaction

Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.

Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.


MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘PICKED’ )
MTL_MATERIAL_TRANSACTIONS (LINE_ID goes as TXN_SOURCE_LINE_ID)
MTL_TRANSACTION_ACCOUNTS
WSH_DELIVERY_DETAILS (RELEASED_STATUS becomes ‘Y’ => ‘Released’ )
WSH_DELIVERY_ASSIGNMENTS
MTL_ONHAND_QUANTITIES
MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')

* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release

6. Ship Confirm

Here is the ship confirm interface program runs in background. Data removed from


WSH_NEW_DELIVERIES. The items on the delivery shipped to customer at this stage
OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’)
WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)
WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)
MTL_TRANSACTION_INTERFACE
MTL_MATERIAL_TRANSACTIONS (linked through Transaction source header id)
MTL_TRANSACTION_ACCOUNTS
Data deleted from MTL_DEMAND, MTL_RESERVATIONS
Item deducted from MTL_ONHAND_QUANTITIES
MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)
MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , 'Issued out of store')

7. Enter Invoice

After shipping the order, order lines are eligible to transfer to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivable interface, this mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order


RA_INTERFACE_LINES_ALL (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in
RA_CUSTOMER_TRX_ALL (CUST_TRX_ID is primary key to link it to TRX_LINES table and TRX_NUMBER is the invoice number)
RA_CUSTOMER_TRX_LINES_ALL (LINE_ATTRIBUTE_1 and LINE_ATTRIBUTE_6 are linked to order number and LINE_ID of the orders)

8. Complete Line

In this stage order line level table get updated with Flow status and open flag.


OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’, OPEN_FLAG “N”)

9. Close Order

This is last step of Order Processing. In this stage only OE_ORDER_LINES_ALL table get updated. These are the table get affected in this step.

OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘closed’, OPEN_FLAG “N”)

To import a routing with operations and resource

To import a routing with operations and resources, you need to populate the following tables:


BOM_OP_ROUTINGS_INTERFACE
BOM_OP_SEQUENCES_INTERFACE
BOM_OP_RESOURCES_INTERFACE


With these three tables, you can create routing header information and assign
operation and resource details.


PROCESS_FLAG
The column PROCESS_FLAG indicates the current state of processing for a row in the interface table. All inserted rows must have the PROCESS_FLAG set to 1.


1 - Pending
2 - Assigned Succeeded
3 - Assign/Validation Failed
4 - Validation Succeeded
7 - Import Succeeded
BOM_OP_ROUTINGS_INTERFACE TABLE.


a. REQUIRED COLUMNS FOR THE BOM_OP_ROUTINGS_INTERFACE TABLE.


You must always enter values for the following required columns when
you insert rows into the BOM_OP_ROUTINGS_INTERFACE table.


PROCESS_FLAG
ASSEMBLY_ITEM_ID
ORGANIZATION_ID
ROUTING_TYPE


If you are creating an alternate routing, you must also enter a value
in the ALTERNATE_ROUTING_DESIGNATOR column.


If the routing you import references a common routing, you must enter a
value in the COMMON_ASSEMBLY_ITEM_ID or the COMMON_ROUTING_SEQUENCE_ID
columns. Routings can only reference common routings that belong to the
same organization. If the routing does not reference a common routing,
the Bill and Routing Interface program defaults the value of the
ROUTING_SEQUENCE_ID for the COMMON_ROUTING_SEQUENCE_ID.


You can specify, in the ROUTING_TYPE column, whether the routing is a
manufacturing routing or an engineering routing. If you do not include
a value for this column, Oracle Bills of Material defaults a value
of 1 (manufacturing), and creates a manufacturing routing. To create
an engineering routing, you must enter a value of 2 (engineering) for
the ROUTING_TYPE column.


For each new row you insert into the BOM_OP_ROUTINGS_INTERFACE table,
you should set the PROCESS_FLAG to 1 (Pending).




b. DERIVED/DEFAULTED VALUES FOR BOM_OP_ROUTINGS_INTERFACE.


The Bill and Routing Interface program derives or defaults most of the
data required to create a manufacturing or engineering routing.
The Bill and Routing Interface program derives or defaults the columns
using the same logic as the Define Routing form or the Define
Engineering Routing form. When you populate a column in the interface
table, the program imports the row with the data you included and does
not default a value.


If you enter a value for the ASSEMBLY_ITEM_NUMBER or COMMON_ITEM_NUMBER
column, you must insert the system item flexfield separator between each
segment of your item number.


INSERT INTO bom_op_routing_interface
(process_flag
,assembly_item_id
,organization_id
,routing_type
,transaction_type
)
VALUES (1
,&inventory_item_id
,&organization_id
,1
,'insert'
);


BOM_OP_SEQUENCES_INTERFACE TABLE.


a. REQUIRED COLUMNS FOR THE BOM_OP_SEQUENCES_INTERFACE TABLE


Each imported record must have a value for the following columns:


PROCESS_FLAG
ROUTING_SEQUENCE_ID
OPERATION_SEQ_NUM
DEPARTMENT_ID
EFFECTIVITY_DATE


You must also specify a value in the ALTERNATE_ROUTING_DESIGNATOR column
if you assign an operation to an alternate routing and have not entered
a value for the ROUTING_SEQUENCE_ID column.


When you insert rows into BOM_OP_SEQUENCES_INTERFACE, you must set the
PROCESS_FLAG to 1 (Pending) for the Bill and Routing Interface program
to process the record.




b. DERIVED/DEFAULTED COLUMN VALUES FOR BOM_OP_SEQUENCES_INTERFACE.


The Bill and Routing Interface program derives or defaults most of the
data required to assign operations to a routing. You can optionally
include a value for derived or defaulted columns, as well as data for
any of the other columns in the interface. The interface program uses
the same logic to derive or default column values in the
BOM_OP_SEQUENCES_INTERFACE table as in the BOM_OP_ROUTINGS_INTERFACE
table. When you populate a column in the interface table, the program
imports the row with the data you included and does not default a
value. However, if you do not enter data in a derived or defaulted
column, the program automatically imports the row with the derived or
defaulted value.


BOM_OP_SEQUENCES_INTERFACE Derived or Defaulted Value
OPERATION_SEQUENCE_ID Sequence BOM_OPERATIONAL_SEQUENCES_S
ROUTING_SEQUENCE_ID From BOM_OP_ROUTINGS_INTERFACE or
BOM_OPERATIONAL_ROUTINGS
LAST_UPDATE_DATE System Date
LAST_UPDATE_BY Userid
CREATION_DATE System Date
CREATED_BY Userid
STANDARD_OPERATION_ID From OPERATION_CODE
DEPARTMENT_ID From DEPARTMENT_CODE
MINIMUM_TRANSFER_QUANTITY 0
COUNT_POINT_TYPE 1
EFFECTIVITY_DATE System Date
BACKFLUSH_FLAG 1
REQUEST_ID From FND_CONCURRENT_REQUESTS
ASSEMBLY_ITEM_ID From ASSEMBLY_ITEM_NUMBER
OPTION_DEPENDENT_FLAG 2
ORGANIZATION_ID From ORGANIZATION_CODE
RESOURCE_ID1 From RESOURCE_CODE1
RESOURCE_ID2 From RESOURCE_CODE2
RESOURCE_ID3 From RESOURCE_CODE3


INSERT INTO bom_op_sequences_interface
(process_flag
,assembly_item_id
,organization_id
,operation_seq_num
,department_id
,effectivity_date
,transaction_type
)
VALUES (1
,&assembly_item_id
,&organization_id
,&operation_seq_num
,&department_id
,&effectivity_date
,'insert'
);

BOM_OP_RESOURCES_INTERFACE TABLE.
a. REQUIRED COLUMNS FOR THE BOM_OP_RESOURCES_INTERFACE TABLE.


You must always enter values for the following required columns when
you insert rows into the BOM_OP_RESOURCES_INTERFACE table:


PROCESS_FLAG
RESOURCE_SEQ_NUM
RESOURCE_ID
OPERATION_SEQUENCE_ID


You must specify a value in the ALTERNATE_ROUTING_DESIGNATOR column if
you assign resources to an alternate routing and have not entered a
value for the ROUTING_SEQUENCE_ID or the OPERATION_SEQUENCE_ID column.


When you insert a row into the BOM_OP_RESOURCES_INTERFACE table, you
must set the PROCESS_FLAG to 1 (Pending) for the Bill and Routing
Interface program to process the record.




b. DERIVED/DEFAULTED COLUMN VALUES FOR BOM_OP_RESOURCES_INTERFACE.


The Bill and Routing Interface program derives or defaults most of the
data required to assign a resource to an operation. You can optionally
include a value for derived or defaulted columns, as well as data for
any of the other columns in the interface. The interface program uses
the same logic to derive or default column values in the
BOM_OP_RESOURCES_INTERFACE table as in the BOM_OP_ROUTINGS_INTERFACE
table and BOM_OP_SEQUENCES_INTERFACE tables. When you populate a
column in the interface table, the program imports the row with the
data you included and does not default a value. However, if you do not
enter data in a derived or defaulted column, the program automatically
imports the row with the derived or defaulted value.


BOM_OP_RESOURCES_INTERFACE Derived or Defaulted Value
OPERATION_SEQUENCE_ID Sequence BOM_OP_SEQUENCES_INTERFACE or
BOM_OPERATION_SEQUENCES_S
RESOURCE_ID From RESOURCE_CODE
ACTIVITY_ID From ACTIVITY
STATDARD_RATE_FLAG From BOM_RESOURCES.STANDARD_RATE_FLAG
ASSIGNED UNITS 1
USAGE_RATE_OR_AMOUNT 1
USAGE_RATE_OR_AMOUNT_INVERSE 1
BASIS_TYPE From BOM_RESOURCES.DEFAULT_BASIS
SCHEDULE_FLAG 2
LAST_UPDATE_DATE System Date
LAST_UPDATED_BY Userid
CREATION_DATE System Date
CREATED_BY Userid
AUTOCHARGE_TYPE From BOM_RESOURCES.AUTOCHARGE_TYPE
REQUEST_ID From FND_CONCURRENT_REQUESTS
ASSEMBLY_ITEM_ID From ASSEMBLY_ITEM_NUMBER
ORGANIZATION_ID From ORGANIZATION_CODE
ROUTING_SEQUENCE_ID From BOM_OP_ROUTINGS_INTERFACE or
BOM_OPERATIONAL_ROUTINGS
INSERT INTO bom_op_resources_interface
(process_flag
,resource_seq_num
,resource_id
,assembly_item_id
,organization_id
,operation_seq_num
,effectivity_date
,transaction_type
)
VALUES (1
,&resource_seq_num
,&resource_id
,&assembly_item_id
,&organization_id
,&operation_seq_num
,&effectivity_date
,'insert'
);

This Article from Uday Somavarapu BlogSpot

Accounts Payable Suppliers Show In Release 12 Receivables Customer

Following Oracle support note has the answers to some common questions..

FAQ: Why Do Accounts Payable Suppliers Show In Release 12 Receivables Customer Form? [ID 733801.1]

R12 AP Tax Tables

select * from ZX_TAXES_VL

select * from ZX_TAXES_TL

select * from ZX_TAXES_b

Items Master and Child Conflict Trouble shooting guide

Troubleshooting Guide for INV_IOI_MASTER_CHILD Errors in Item Import [ID 429924.1]

AP Invoices conversion process

The process for AP invoice program is as follows:

Excel Sheet -> Data file -> SQL Loader (Control file) -> Staging Tables -> Run Validation program -> Interface Tables -> Run the Payables Open Invoice Import Program -> Base Tables

Staging tables:
Create a custom staging table XX_AP_INVOICE_INTERFACE, XX_AP_INVOICE_LINES_INTERFACE which should be same as seeded interface table AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE.

Interface Tables:
1. AP_INVOICES_INTERFACE
2. AP_INVOICE_LINES_INTERFACE

Base Tables:
1. AP_INVOICES_ALL
2. AP_INVOICE_DISTRIBUTIONS_ALL
3. AP_PAYMENT_SCHEDULES_ALL

Process Flow:
-----------------
1. Excel sheet template is prepared which contains all the columns in AP_INVOICES_INTERFACE and AP_INVOICE_LINES table.
2. Convert the Excel Sheet which contains data to be interfaced given by the user into CSV file format.
3. Format the CSV file so that it contains only the relevant data separated with commas.
4. Rename the CSV into DAT file.
5. Prepare the Control File.
6. Run SQL Loader Script.
7. Check the Log file for any errors. In case of any errors rectify it and re-run the SQL Loader Script.
8. Run the PL/SQL Program to validate staging table data and load to Interface tables.
9. Run the Payables Open Invoice Import Program.
10. Check for the status in the Interface Table.
11. Query for the data in the front-end.

Script to Cancel AP Invoices

/* Formatted on 2010/08/09 20:15 (Formatter Plus v4.8.0) */
CREATE OR REPLACE PROCEDURE cancel_invoices (ip_operating_unit IN VARCHAR2)
AS
l_resp_id NUMBER;
l_appl_id NUMBER;
l_user_id NUMBER := apps.fnd_global.user_id;
l_org_id NUMBER := apps.fnd_global.org_id;
l_message_name VARCHAR2 (1000);
l_invoice_amount NUMBER;
l_base_amount NUMBER;
l_tax_amount NUMBER;
l_temp_cancelled_amount NUMBER;
l_cancelled_by VARCHAR2 (1000);
l_cancelled_amount NUMBER;
l_cancelled_date DATE;
l_last_update_date DATE;
l_original_prepayment_amount NUMBER;
l_pay_curr_invoice_amount NUMBER;
l_token VARCHAR2 (100);
l_boolean BOOLEAN;
err_msg VARCHAR2 (2000);

CURSOR invoice_cur
IS
SELECT aia.invoice_id, aia.last_updated_by, aia.last_update_login,
aia.gl_date, aia.invoice_num
FROM xx_ap_invoices_conv_stg a,
ap_invoices_all aia,
ap_invoice_lines_all aila
WHERE a.ls_inv_num = aia.invoice_num
AND a.ls_org_id = aia.org_id
AND aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND aia.payment_status_flag = 'N'
AND NVL (aila.cancelled_flag, 'N') <> 'Y';
--- AND aia.invoice_num = '65431';
BEGIN
BEGIN
SELECT DISTINCT fr.responsibility_id, frx.application_id
INTO l_resp_id, l_appl_id
FROM apps.fnd_responsibility frx,
apps.fnd_responsibility_tl fr
WHERE fr.responsibility_id = frx.responsibility_id
AND UPPER (fr.responsibility_name) LIKE
UPPER (DECODE (ip_operating_unit,
'OU USA MA', 'Payables Manager',
'OU USA WI', 'OU USA WI_Payables Manager',
'OU Austria', 'OU AUSTRIA_Payables Manager',
'OU China', 'OU CHINA_Payables Manager'
)
);

DBMS_OUTPUT.put_line ('l_resp_id => ' || l_resp_id);
DBMS_OUTPUT.put_line ('l_appl_id => ' || l_appl_id);
EXCEPTION
WHEN OTHERS
THEN
err_msg :=
'Error Occured while Deriving responsibility id' || SQLERRM;
apps.fnd_file.put_line
(apps.fnd_file.output,
'Error Occured while Deriving responsibility id'
);
END;

mo_global.set_policy_context ('S', l_org_id);
apps.fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR l_inv_rec IN invoice_cur ----(l_org_id)
LOOP
DBMS_OUTPUT.put_line
( 'Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: '
|| l_inv_rec.invoice_num
);
DBMS_OUTPUT.put_line
('**************************************************************');
l_boolean :=
ap_cancel_pkg.ap_cancel_single_invoice
(p_invoice_id => l_inv_rec.invoice_id,
p_last_updated_by => l_inv_rec.last_updated_by,
p_last_update_login => l_inv_rec.last_update_login,
p_accounting_date => l_inv_rec.gl_date,
p_message_name => l_message_name,
p_invoice_amount => l_invoice_amount,
p_base_amount => l_base_amount,
p_temp_cancelled_amount => l_temp_cancelled_amount,
p_cancelled_by => l_cancelled_by,
p_cancelled_amount => l_cancelled_amount,
p_cancelled_date => l_cancelled_date,
p_last_update_date => l_last_update_date,
p_original_prepayment_amount => l_original_prepayment_amount,
p_pay_curr_invoice_amount => l_pay_curr_invoice_amount,
p_token => l_token,
p_calling_sequence => NULL
);
DBMS_OUTPUT.put_line ('l_message_name => ' || l_message_name);
DBMS_OUTPUT.put_line ('l_invoice_amount => ' || l_invoice_amount);
DBMS_OUTPUT.put_line ('l_base_amount => ' || l_base_amount);
DBMS_OUTPUT.put_line ('l_tax_amount => ' || l_tax_amount);
DBMS_OUTPUT.put_line ( 'l_temp_cancelled_amount => '
|| l_temp_cancelled_amount
);
DBMS_OUTPUT.put_line ('l_cancelled_by => ' || l_cancelled_by);
DBMS_OUTPUT.put_line ('l_cancelled_amount => ' || l_cancelled_amount);
DBMS_OUTPUT.put_line ('l_cancelled_date => ' || l_cancelled_date);
DBMS_OUTPUT.put_line ('P_last_update_date => ' || l_last_update_date);
DBMS_OUTPUT.put_line ( 'P_original_prepayment_amount => '
|| l_original_prepayment_amount
);
DBMS_OUTPUT.put_line ( 'l_pay_curr_invoice_amount => '
|| l_pay_curr_invoice_amount
);

IF l_boolean
THEN
DBMS_OUTPUT.put_line ( 'Successfully Cancelled the Invoice => '
|| l_inv_rec.invoice_num
);
COMMIT;
ELSE
DBMS_OUTPUT.put_line ( 'Failed to Cancel the Invoice => '
|| l_inv_rec.invoice_num
);
ROLLBACK;
END IF;
END LOOP;
END cancel_invoices;

Item Import Overview

Item Import Overview

Use the item import process to import items from your legacy system or PDM system. You can import items from any source into Oracle Inventory and Oracle Engineering. When you import items through the Item Interface, you create new items in your Item Master organization or assign existing items to additionalorganizations. You can specify values for all the item attributes, or you canspecify just a few attributes and let the remainder default or remain Null. You can also specify an item template for each item and inherit attribute values from the template. The Item Interface also lets you import revision details, including past and future revisions and effectivity dates. Validation of imported items is done using the same rules as the item definition forms, so you are insured of valid items.The Item Interface reads data from two tables for importing items and item details. You use the MTL_SYSTEMS_ITEM_INTERFACE table for new item numbers and all item attributes. This is the main item interface table, and can be the only table you choose to use. If you are importing revision details for new items, you can use the MTL_ITEM_REVISIONS_INTERFACE table. A third table, MTL_INTERFACE_ERRORS, is used for error tracking of all items that the Item Interface fails. You can import item categories using interface table called as MTL_ITEM_CATEGORIES_INTERFACE.Before you use the Item Interface, you must write and run a custom program that extracts item information from your source system and inserts it into the MTL_SYSTEM_ITEM_INTERFACE table, and (if revision detail is included) the MTL_ITEMS_REVISIONS_INTERFACE table. After you load the items into these interface tables, you run the Item Interface to import the data. The Item Interface assigns defaults, validates data you include, and then imports the new items. You may also specify an item template for each item being imported. You must import items into the Item Master organization before you import itemsinto additional organizations. You can accomplish this by specifying only your Item Master organization on first run of the Item Interface. Once this has completed, you can run the Item Interface again, this time specifying an additional or all organizations. You can also use the Item Interface to import a single item material cost and material overhead, and revision details.Below we will walk through few scenarios of item import. Scenario:

1Testing the basic item import with minimum columns populated.

First always try to populate the master organization with a sample record and test.1. How do I know my organization code and organization id ?Select organization_code , organization_id from org_organization_definitionsorder by organization_code;Sample out put is below.ORG ORGANIZATION_ID--- -------------------------------V1 204M1 207M2 209M3 606M4 1641M5 1642M6 1643M7 1644....2. How do I know which organization is master to a child organization?select a.organization_id ,a.organization_code,a.master_organization_id,b.organization_code from mtl_parameters a, org_organization_definitions bwhere a.master_organization_id = b.organization_idorder by a.organization_code;Sample out put is belowORGANIZATION_ID ORG MASTER_ORGANIZATION_ID ORG--------------------------- ------- ---------------------------------------- ------- 204 V1 204 V1 207 M1 204 V1 209 M2 204 V1 606 M3 204 V1 1641 M4 204 V1 1642 M5 204 V1 1643 M6 204 V1 1644 M7 204 V1 .....In the above example for organizations M1 to M7, V1 organization is the master organization.Populating V1 organization with one new item using just the basic columns.Please truncate all IOI interface tables before loading mtl_system_items_interface: ex. TRUNCATE TABLE TRUNCATE TABLE INV.MTL_SYSTEM_ITEMS_INTERFACE;TRUNCATE TABLE INV.MTL_INTERFACE_ERRORS;TRUNCATE TABLE INV.MTL_ITEM_REVISIONS_INTERFACE;TRUNCATE TABLE INV.MTL_ITEM_CATEGORIES_INTERFACE;Note: Truncate frees space, delete does not free space.insert into mtl_system_items_interface(process_flag,set_process_id,transaction_type,organization_id,segment1,description)values(1,1,'CREATE',204,'TESTITEMIMPORT', 'Testing Item Import');commit;In the above insert Process_Flag = 1. Only when process_flag = 1 will the item import program process the record.Set_Process_Id = 1. You can set which ever number you want for this column. This is basically used like a batch number. You can tell item import which set of records to process by entering a set_process_id.Transaction_Type is 'CREATE' when you are inserting new records and 'UDATE' when you are updating existing records.We are using organization_id 204 which is for master organization V1.Note: If you have more than one segment defined for items please populate all those segments.To check which segments to be populated please got to Setup > Flexfields > Key > Segments . Now query up 'System Items' by putting it in flexfield title.Select segments button, and now you should be able to see the different segments being defined.Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to master items screen and query on 'TESTITEMIMPORT' the item that we had populated and you should be able to see the item.Similarly you can import the same item for the child organization. Eg. we want item 'TESTITEMIMPORT' in organization M1 say Seattle manufacturing then all I have to do is replace the organization_id by M1 organization_id. Now the insert statement will look like this.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description)values(1,1,'CREATE',207,'TESTITEMIMPORT', 'Testing Item Import');commit;Run the item import with the same parameters and then check if the concurrent request has completed successfully.Now do Change Organization and go to Organization Item screen and query up TESTITEMIMPORT and you should be able to see the item in M1 organization.Scenario: 2~~~~~~~~~~~~~To import items and use item templates.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^To see all template names run the following sql.select template_id,template_name,description from MTL_ITEM_TEMPLATES;To see all the attributes related to template run the following sql.select template_id,attribute_name,enabled_flag,report_user_value from MTL_ITEM_TEMPL_ATTRIBUTES where template_id = &template_id;Please subsitute the template id from the 1st query in the second query to see the attribute values.In our example I am going to use Purchasing Template.select template_id,template_name,description from MTL_ITEM_TEMPLATESwhere template_id = 259;template_id template_name description----------- ------------- ---------------- 259 Purchased Item Purchased ItemWe are going to insert a new item in the master organization and use the purchasing template.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description,TEMPLATE_ID)values(1,1,'CREATE',204,'TESTTEMPLATE', 'Testing Item Import With Template',259);commit;Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to master items screen and query on 'TESTTEMPLATE' the item that we had populated and you should be able to see the item. Now you will see that the purchasing attributes are set because we used the purchasing template.Note: Instead of using template_id we can use template_name and you should see the same effect.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description,TEMPLATE_NAME)values(1,1,'CREATE',204,'TESTTEMPLATE', 'Testing Item Import With Template','Purchased Item');commit;Scenario: 3~~~~~~~~~~~~To import items and material cost associated to it.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^First check which cost elements are defined for the organizaion.select cost_type_id,cost_type,description from CST_COST_TYPES;Output looks like this.cost_type_id cost_type description------------ ------------- ------------------------------------ 1 Frozen Frozen Standard Cost Type 2 Average Average Cost Type 3 Pending Pending Standard Cost Type.....To find the sub element name defined for your organization for a particular cost type please check bom resources table.In our example we are checking for material sub element in organization M1 with organization_id = 207 for cost_code type 1.select resource_code,description,cost_element_id,cost_code_type from bom_resources where organization_id = 207and cost_code_type= 1;resource_code description cost_element_id cost_code_type------------- ----------------- --------------- --------------Material Material Sub element 1 1Labor Labor Sub element 1 1Expense Expense Sub element 1 1In our scenario we want to create a new item in organization M1 along with its material cost (say $11) assuming the same item is already created in master organization V1.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description,template_id,MATERIAL_COST,MATERIAL_SUB_ELEM)values(1,1,'CREATE',207,'TESTCOSTMATERIAL', 'Testing Item Material Cost Import',259,11,'Material');commit;In the above sql you might have observed we are populating MATERIAL_COST and MATERIAL_SUB_ELEM.Similary we can use MATERIAL_SUB_ELEM_ID, MATERIAL_OH_RATE, MATERIAL_OH_SUB_ELEM and MATERIAL_OH_SUB_ELEM_ID colums.To check the subelements you can go to Cost management responsibility for your organization and under Setup > Sub-Elements you will see the respective subelements being defined.Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'TESTCOSTMATERIAL' the item that we had populated and you should be able to see the item. Go to Tools > Item Costs and then hit Open button from Item Cost Summary screenand you should see the details. In out example we see a material cost of $11 for the item in cost type 'Frozen'(Standard Costing).NOTE: Item import cannot be used to update item costs.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~You have to populate CST_ITEM_CST_DTLS_INTERFACE,CST_RESOURCE_COSTS_INTERFACE,CST_RES_OVERHEADS_INTERFACE and CST_DEPT_OVERHEADS_INTERFACE accordingly.The concurrent request can be launched by navigating to cost->cost mass edits->Import Cost Information.The parameters for the Cost Import concurrent program and their description isas follows : Import Cost Option : A LOV is provided from which the user can select one of the import options which may be either to import Only item costs , Only resource costs , Only overhead rates or all the cost information . Option Table from which data is processed ---------- -------------------------------------------- Only item cost cst_item_cst_dtls_interface Only resource costs cst_resource_costs_interface Only overhead rates cst_res_overheads_interface , cst_dept_overheads_interface All Cost Information From all the four interface tablesMode to run this request : A LOV is provided with possible two values , 'Insert new cost' or 'Remove and replace cost'. The 'Insert new cost' mode , is useful if you are importing large # of items and are not sure if that Item/Organization/Cost Type combination already exists in the production tables, if it does then the row in the interface table would be flaged as errored and not imported.This would prevent any accidental overwrite of already existing data. With 'Remove and replace cost' mode all the previous cost information for this item, cost_type and organization combination will be deleted from the production tables and the new information will overwrite (replace) the already existing one. Group Id Option : A LOV is provided from which the user can either select 'ALL' or 'Specific Group Id' . If the user wishes to submit multiple Cost Import process requests he can do so by submitting one request per group id. For doing so the data in the interface tables should be stamped with distinct group id value by using the NEXTVAL from the sequence generator CST_LISTS_S .The use of this sequence geneartor is a MUST for generating multiple groups or may lead to data corruption as these interface tables are used by other processes too. If the user selects "ALL" from the list then a group ID generated by a sequence will replace the group ID in the interface tables (if any) and all the unprocessed rows from the four interface table (viz. cst_item_cst_dtls_interface , cst_resource_costs_interface , cst_res_overheads_interface , cst_dept_overheads_interface ) will be processed in one run. Cost type to import to : The user is provided with a LOV from which he needs to select the cost type in which he wishes to import the cost information. Even if the user has populated a cost type or cost type ID in the interface tables, it would be overwritten with the one that is selected here. The cost types that the user can pick from is restricted to the multi-org, updateable cost types. Delete succesfull rows : This parameter decides whether the successfully processed rows should be deleted from the interface tables at the end of the run. If the user selects 'Yes' then all the successful rows be deleted, basically rows that do not have their error flag set to "E".Importing directly into Frozen/Average cost type (i.e non updateable cost types)and merging of new cost with existing costs is not supported at this time andwould still have to be processed by cost update routines. Also when importingthe costs from the interface table ,material overhead defaults (if any)specified for an Organization/Category would not be respected.Minimum columns in each table that the user needs to provide1. CST_ITEM_CST_DTLS_INTERFACE The columns that the user has to provide are a. Inventory_item_id b. organization_ID or organization_code. c. resource_ID or resource_code for cost elements other than 1(material). If we are importing cost into material cost element and default material subelement has been specified on 'Define Organization parameters' form then that would be respected unless the user overides it with a value in this column. d. usage_rate_or_amount e. cost_element_ID or cost_element f. Process Flag (must be set to 1) We default values for based_on_rollup, shrinkage_rate, inventory_asset_flag, lot_size from the row from CST_ITEM_COSTS for this item and the default cost type(default cost type of the cost type specified to import the costs into). If there is no such row already defined in CST_ITEM_COSTS, then, the values for these 4 columns also need to be specified and they have to be the same for all rows of this item, cost type and organization combination.CST_RESOURCE_COSTS_INTERFACE a. Resource_ID or resource_code b. organization_ID or organization_code c. resource_rate d. Process_flag (must be set to 1)CST_RES_OVERHEADS_INTERFACE a. Resource_ID or resource_code b. Overhead_ID or overhead c. Organization_ID or organization_code d. Process_flag(must be set to 1)CST_DEPT_OVERHEADS_INTERFACE a. Department_ID or department b. Overhead_ID or overhead c. Organization_ID d. Rate_or_amount e. Process_flag(must be set to 1)The other columns will be defaulted.This is available for only 11.5.9 and above customers.Customers on 11.5.8 can apply patch 2193391 to get this functionality.Please refer to costing Manuals for further details.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^In case you have any questions please log a tar with costing group.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Sample cost update statement is belowINSERT INTO CST_ITEM_CST_DTLS_INTERFACE(INVENTORY_ITEM_ID,ORGANIZATION_ID,COST_TYPE,RESOURCE_CODE,USAGE_RATE_OR_AMOUNT, COST_ELEMENT_ID,PROCESS_FLAG)VALUES(9935,207,'Pending','Material',17,1,1);commit;Using this I update the pending cost for the item from $11 to $17.Now once the pending cost is updated you will have to use 'Update Standard Cost'Concurrent program to update Frozen cost for the item from the pending cost.Scenario: 4~~~~~~~~~~~~~~To import item and revisions associated to it.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^When ever you import a new item based on the Starting revision specified under'Revison/Lot/Serial' tab in organization parameters screen the default revison gets assigned to the item. For eg. the default revision in V1 organization is 'A' when we do item import this revision gets associated to the item.Now lets import a new item with revision 'B' which is not the default revision.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description,REVISION)values(1,1,'CREATE',204,'TESTREVB1', 'Testing Item Revision','B');Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'TESTREVB1' the item that we had populated and you should be able to see the item. Go to Tools > Revisions and then you should be able to see two records one with revision 'A' which is the default revision and another with revision 'B' that we had populated.In case you want to import a new revision say revision 'C'. You directlypopulate the MTL_ITEM_REVISIONS_INTERFACE table and then run item import.insert into MTL_ITEM_REVISIONS_INTERFACE(item_number,revision, revision_label,implementation_date, effectivity_date, process_flag, organization_id ,set_process_id, transaction_type)values('TESTREVB1','C','C',sysdate,sysdate,1,204,1,'CREATE');Commit;Now run the item import.In Process Set enter 1 as we had used 1 in the set_process_id column.Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.When you query up the item 'TESTREVB1' You will see the new revision 'C'.Scenario: 5~~~~~~~~~~~~~~Lets take into consideration we have to import more than two revisions along ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^with item creation. We will be populating MTL_ITEM_REVISIONS_INTERFACE. The key point here is the IMPLEMENTATION_DATE should increment cronologically.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description)values(1,1,'CREATE',204,'TESTREVB2', 'Testing Item Revision');insert into MTL_ITEM_REVISIONS_INTERFACE(item_number,revision, revision_label,implementation_date, effectivity_date, process_flag, organization_id ,set_process_id, transaction_type)values('TESTREVB2','A','A',sysdate,sysdate,1,204,1,'CREATE');insert into MTL_ITEM_REVISIONS_INTERFACE(item_number,revision, revision_label,implementation_date, effectivity_date, process_flag, organization_id ,set_process_id, transaction_type)values('TESTREVB2','B','B',sysdate+.0003472,sysdate+.0003472,1,204,1,'CREATE');insert into MTL_ITEM_REVISIONS_INTERFACE(item_number,revision, revision_label,implementation_date, effectivity_date, process_flag, organization_id ,set_process_id, transaction_type)values('TESTREVB2','C','C',sysdate+.0023472,sysdate+.0023472,1,204,1,'CREATE');COMMIT;Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'TESTREVB2' the item that we had populated and you should be able to see the item. Go to Tools > Revisions and then you should be able to see three records one with revision 'A' , another with revision 'B' and the thrid one is 'C' that we had populated.Note: We have been inctementing the minutes section by sysdate+.0003472 and sysdate+.0023472 so that the implementation date is in cronological order.Scenario: 6~~~~~~~~~~~~~~~~Lets take into consideration we have to import item categories along with items.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^select category_id,structure_id,segment1,description,category_concat_segs,structure_name from mtl_categories_vwhere segment1 = 'DAIRY'order by category_id,structure_id;category_id structure_id segment1 description category_concat_segs structure_name --------------------------------------------------------------------------------------- 1440 51466 DAIRYDairy Products DAIRY Inventory Class 1861 51467 DAIRYDairy Products DAIRY Planning Class 1869 51470 DAIRYDairy Products DAIRY Sales Class 1886 51766 DAIRYDAIRY DAIRY GL Product Lineselect category_set_id,category_set_name,description,structure_name,structure_idfrom mtl_category_sets_v where category_set_name = 'Inventory Class';category_set_id category_set_name description structure_namestructure_id--------------------------------------------------------------------- 90 Inventory Class Inventory Class Inventory Class 51466In out example we want to use category 'DAIRY' which has a category_id of 1440 and category set 'Inventory Class' which has category_set_id of 90.You can go to the application Setup > Items > Categories > Category Codes and using examine get the category id.You can go to the application Setup > Items > Categories > Category Sets and using examine get the category set id.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description)values(1,1,'CREATE',204,'TESTCATG1', 'Testing Item Categories');insert into MTL_ITEM_CATEGORIES_INTERFACE(item_number,category_set_id, category_id,process_flag, organization_id ,set_process_id, transaction_type)values('TESTCATG1',90,1440,1,204,1,'CREATE');In the above insert statement you could use CATEGORY_SET_NAME and CATEGORY_NAME instead of CATEGORY_SET_ID and CATEGORY_ID.commit;Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'TESTCATG1' the item that we had populated and you should be able to see the item. Go to Tools > Categoriess and then you should be able to see category Dairy associated to the item.To verify you can run the following querySelect * from MTL_ITEM_CATEGORIES where inventory_item_id in (select distinct inventory_item_id from mtl_system_items_b where segment1 = 'TESTCATG1');It shows one record.NOTE: Populating set_process_id is mandatory for categories import. Please putthe same set process id when you run the import program.Updating exsisting item categories:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Updated transaction can only be performed as a combination of DELETE assignment, then CREATE.Lets take our above example of item 'TESTCATG1' which has now category 'DAIRY'associated with it. I want to update category 'DAIRY' with a new category 'BAKERY' whose category_id is 1436 for item 'TESTCATG1'.insert into MTL_ITEM_CATEGORIES_INTERFACE(item_number,category_set_id, category_id,process_flag, organization_id ,set_process_id, transaction_type)values('TESTCATG1',90,1440,1,204,1,'DELETE');insert into MTL_ITEM_CATEGORIES_INTERFACE(item_number,category_set_id, category_id,process_flag, organization_id ,set_process_id, transaction_type)values('TESTCATG1',90,1436,1,204,1,'CREATE');commit;Run item import.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 2 in create or update items field as we are updating the exsisting item and changing its category. Hit OK button.You can check view requests to check whether your request has completed successfully.When you query up item 'TESTCATG1' and select the categories from tools you will find that the category 'BAKERY' is now asscoiated with the item.Scenario: 7~~~~~~~~~~~~Lets take into consideration we have to import item catalogs along with items.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^In our example we want to asscoiate 'ADSL Modem' catalog to our item.select ITEM_CATALOG_GROUP_ID,SEGMENT1 from MTL_ITEM_CATALOG_GROUPS_Bwhere ITEM_CATALOG_GROUP_ID = 161;ITEM_CATALOG_GROUP_ID SEGMENT1--------------------- ---------------------------------------- 161 ADSL Modemselect ITEM_CATALOG_GROUP_ID,ELEMENT_NAME,ELEMENT_SEQUENCE,DESCRIPTION from mtl_descriptive_elementswhere ITEM_CATALOG_GROUP_ID = 161;ITEM_CATALOG_GROUP_ID ELEMENT_NAME ELEMENT_SEQUENCE DESCRIPTION--------------------- ------------ ---------------- ----------- 161 2wire 10 2wire 161 linksys 20 linksysdescinsert into mtl_system_items_interface(process_flag, set_process_id, transaction_type,organization_id,segment1,description,ITEM_CATALOG_GROUP_ID)values(1,1,'CREATE',204,'TESTCATA1', 'Testing Item Catalog',161);commit;Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'TESTCATA1' the item that we had populated and you should be able to see the item. Go to Tools > Catalogs and then you should be able to see 'ADSL Modem' along with descriptive elements '2wire' and 'linksys'. In 11.5.9 you have to manually enter the values for descriptive elements. In 11.5.10 API to import descriptive element values will be present.Scenario: 8~~~~~~~~~~~~Lets understand how to update existing items.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Lets say I have an item 'ODC' which has no buyer code in Seattle organization (M1 organization code). I want to update and asscoiate 'West, Rod' as buyer to the item 'ODC'.First I check under Setup > Items > Attribute Contol to check if it controlled at the organization level or master level. In our case the attribute is controlled at the Org Level.Now we check if Rod exists in the list of valid buyers.select agent_id,agent_name,start_date_active,end_date_activefrom PO_AGENTS_V where agent_name like '%Rod%';agent_id agent_name start_date_active end_date_active-------- ---------- ----------------- --------------- 10183 West, Rod 17-FEB-04select inventory_item_id from mtl_system_items_bwhere segment1 = 'ODC'and organization_id = 207;207 organization id is for M1(Seattle organization)INVENTORY_ITEM_ID----------------- 9372Now use the above values in the insert script.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type, organization_id, inventory_item_id, BUYER_ID)values (1,1,'UPDATE',207,9372,10183);commit;Now you will observe that the transaction type is 'UPDATE' because we are updating an existing record. I am also using inventory_item_id because when you use id's the performance is better. And also when running the item import I use value 2 in create or update items field.Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 2 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'ODC' item in M1 organization. Under purchasing tab you will see that the buyer 'West, Rod' populated.Note:INCOIN does not support updating item numbers. Enhancement request 6507704 already logged for this.Scenario: 9~~~~~~~~~~~~~In this scenario you will learn how to populate character and number columns^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ and make it null.^^^^^^^^^^^^^^^^^Just from out above example we have buyer 'West, Rod' populated for item 'ODC'. Now for some reason we want to make null for buyer for item ODC.Buyer id column is a number column. To make it null we have to populate -999999.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type, organization_id, inventory_item_id, BUYER_ID)values (1,1,'UPDATE',207,9372,-999999);commit; Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 2 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'ODC' item in M1 organization. Under purchasing tab you will see that the buyer field is updated to null.Suppose planner 'S.Peters' is asscoiated to item ODC. You want to make it null.PLANNER_CODE in mtl_system_items_interface is a charracter type. To make it null we have to use '!'.insert into mtl_system_items_interface(process_flag, set_process_id, transaction_type, organization_id, inventory_item_id, PLANNER_CODE)values (1,1,'UPDATE',207,9372,'!');commit;Now run the item import program ie. Item > Import > Import Items.In Process Set enter 1 as we had used 1 in the set_process_id column. Now enter 2 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.Now go to organization items and query on 'ODC' item in M1 organization. Under general planning tab you will see that the planner is updated to null.Note: To make number columns null use -999999 and to make character columns -----------------------------------------------------------------------------null use '!' exclamation.-------------------------Frequently Asked Questions (FAQ):~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~a. How do I know what error has occurred?^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Ans . The process_flag in mtl_system_items_interface will be 3. Now the error message will be in mtl_interface_errors table.The record in mtl_system_items_interface will have a transaction_id. There will be corresponding record with error code and explanation in mtl_interface_errors.select transaction_id from mtl_system_items_interface where process_flag = 3;Now substitue the &transaction_id value in the following sql by the value that you get in the ablove sql. You should be able to see the error.select message_name,column_name,error_message from mtl_interface_errors where transaction_id = &transaction_id;b. What things should I check if I getting errors?^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Ans. First check if you are populating the correct values in all the fields. Then check if there are any mandatory descriptive flexfield columns. You can go to application setup > flexfields > descriptive > segments and on query type ?Items? in title field and then check each segment whether it is mandatory and whether it is doing any validation against some pre-existing values in some value set. If so check if the corresponding attributes are populated properly. Many times user forgets to populate mandatory values and item import fails.Also check for invalid objectsSelect object_name,object_type from all_objects where status = 'INVALID';If any invalid objects please recompile those and retest item import.c. I run item import but records are not getting processed. The process_flag^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^is still 1.^^^^^^^^^^^Ans. Check under sysadmin> concurrent managers whether inventory namager is running. Then check if there is suffcient memory and extent spaces. Use notes 1019721.6 and 1020085.6 to determine if there is a memory or extents issue. Please check if there is sufficient roll back segments , system global area and tablespace.Please use the following truncate . This frees up space.TRUNCATE TABLE INV.MTL_SYSTEM_ITEMS_INTERFACE;TRUNCATE TABLE INV.MTL_INTERFACE_ERRORS;TRUNCATE TABLE INV.MTL_ITEM_REVISIONS_INTERFACE;TRUNCATE TABLE INV.MTL_ITEM_CATEGORIES_INTERFACE;Now try importing one record and see if you are able to import it successfully.If you see ORA-1654 happens while inserting records into mtl_pending_item_status table in the log file (01654, 00000, "unable to extend index %s.%s by %s in tablespace %s") please use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated. d. What do I do to check more in to errors and see where the import is failing?^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Ans. From sysdamin please set the following profile options.INV: Debug Trace: set Yes to turn on the debug functionality. INV:Debug Level: set 10 , to print the detail debug messages.INV: Debug file (Including the complete path): The path is one of the directories from the valid directories defined in the obtained v$parameter through the following sql : select value from v$parameter where name = 'utl_file_dir'; Make sure to include the file name at the end of the path, e.g. '/<>/trx_mgr.log'MRP: Debug Mode 'Yes'2. After setting the above system profile values go to Sysadmin > Concurrent > Program > Define. Query on short name 'INCOIN'. Now check enable trace and save. Now run the item import the log file of item import will give more details. Note: A log file may not be generated in the directory, what developmentwants is the concurrent request log file after the above settings are done.To identify the correct trace file please run the following queryselect request_id,oracle_process_idfrom fnd_concurrent_requestswhere request_id = &request_id;Please substitute &request_id by the correct request id from the concurrent program.The trace file in the trace directory would have the number thatyou get for oracle_process_id from the above query as a part of the file name.eg: if oracle_process_id = 22771The file could be ora22771.trce. 11.5.9 Item import issues and patches to be applied.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^1. Cannot create items - Patch 32154732. INV_IOI_REV_BAD_ORDER error message for revisions - Patch 32263593. Item import fails with no error message - Patch 32263594. Item import completes but process_flag = 4 and set_process_id = -999 - Patch 32263595. If duplicate records for one master IOI does not import child recs for other master records. The error message that you will get is "This Child Item has no Master Item record in MTL_SYSTEM_ITEMS" -Patch 3474468f. List of documents required while logging a tar on item import.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^1. Dump of records in all 4 interface tables (in excel spread sheet includingcolumn headers) MTL_SYSTEM_ITEMS_INTERFACE,MTL_INTERFACE_ERRORS,MTL_ITEM_REVISIONS_INTERFACE and MTL_ITEM_CATEGORIES_INTERFACE before running import and after running import.2. Output of BOMCHECK.sql (See NOTE:244145.1)3. Raw Trace, Tkprof of trace and log files after doing settings recommended in section d(What do I do to check more in to errors and see where the import is failing?) above.4. Version of INCOIN from the server.G. Some examples of errors and what it might mean^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^a.Some times users are unable to import items to child organization. They mightget error DEFAULT_INCLUDE_IN_ROLLUP_FLAG,INVENTORY_ITEM_STATUS_CODE,INVENTORY_PLANNING_CODE,PLANNER_CODE,PLANNING_MAKE_BUY_CODE,FIXED_LOT_MULTIPLIER,ROUNDING_CONTROL_TYPE,CARRYING_COST Check the corresponding attribute values.If planner code is set at Master level please make it organization level.(Ref. Oracle Inventory User Guide pg 5-52 - General Planning Attribute Group ) "Planner Code This attribute is controlled at the Organization level only."b.Some times users might get MESSAGE NAME : BOM_PARSE_ITEM_ERROR or MESSAGE NAME : INV_IOI_ERRERROR MESSAGE : *** BAD RETURN CODE b ***Please do the following1)Please see that setup > Receiving > (Purchasing and Receiving) setups are complete.2)Please auto extend all MTL Tables and MTL Indexes.3)Please auto extend Table Space.4)Re-Link all Inventory libraries. Relink INCOIN.o.5)Compile all Inventory Key Flex Fields.6)Compile all invalid objects.7)Please check all INV profile options are set.8)Now truncate the interface tables and populate it with few records and run import. The error should go.H. Is there an API for Item creation and updation^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ INV_ITEM_GRP.Create_item - For creating the items INV_ITEM_GRP.Update_item - For updating the items (For details on calling on this API check file INVGITMB.pls)- file in /invdev/inv/11.5/patch/115/sqlOr useEGO_ITEM_PUB.Process_item - For creating/updating items (For details on calling on this API check file EGOPITMB.pls) - file in /egodev/ego/11.5/patch/115/sql

Increasing Performance in cursor

To increase the performance in the cursor use below statement in the cursor
/*+PARALLEL(OL, 10) */

CURSOR cur_month_usage
IS
SELECT /*+PARALLEL(OL, 10) */
ol.sold_to_org_id,
(SELECT hc.account_number
FROM apps.hz_cust_accounts hc
WHERE 1 = 1
AND hc.cust_account_id = ol.sold_to_org_id)

Examples of the to_date function

Examples of the to_date function might include:
to_date('10-12-06','MM-DD-YY')
to_date('jan 2007','MON YYYY')
to_date('2007/05/31','YYYY/MM/DD')
to_date('12-31-2007 12:15','MM-DD-YYYY HH:MI')
to_date('2006,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS')
to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss')
to_date('022002','mmyyyy')
to_date('12319999','MMDDYYYY')
to_date(substr( collection_started,1,12),'DD-MON-YY HH24')
to_date('2004/10/14 21', 'yyyy/mm/dd hh24')
TO_DATE(First_Load_Time, 'yyyy-mm-dd/hh24:mi:ss'))*24*60)

To see the current system date and time with time zone use CURRENT_DATE function
select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;

select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
NOW NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53