Oracle Inventory Transaction Interface

MTL_TRANSACTIONS_INTERFACE Table

This is the Interface between non-Inventory applications and the Inventory Transactions module. For example, if non-Inventory modules such as WIP (Work In Progress) and OE (Order Entry) want to update Inventory, has to first pass their records to the MTL_TRANSACTIONS_INTERFACE (MTI) for validation.

This form queries the MTL_TRANSACTIONS_INTERFACE table from front-end:
         Navigate Inventory Responsibility -> Transactions -> Transaction Open Interface

Make sure that status for material transaction manager is “Active”. If the status says “Inactive” Launch "Material Transaction Manager" from Inventory setup module.

‘Inventory Super User’ responsibility -> Oracle Inventory -> Setup -> Transactions -> Interface Manager -> Select Organizations/Sub-Inventory  -> Material Transaction ->

Menu Bar -> Tools -> Launch Manager

This will schedule a concurrent program called 'Process transactions interface' (Short Name: INCTCM) every 5 minutes. INCTCM reads records from the MTI table, validates them and moves the successful transactions into the MTL_MATERIAL_TRANSACTIONS_TEMP table, and submits Transaction workers (sub-processes: 'Inventory transaction worker', Short Name: INCTCW) which then process these records through inventory. Only records will be picked up by the Transaction Manager and assigned to a Transaction Worker.

It is important to note that Only records of MTI table will be picked up by the Transaction Manager and assigned to a Transaction Worker for those the following fields are set as follows:

TRANSACTION_MODE = 3,
LOCK_FLAG = 2,
PROCESS_FLAG = 1

 If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION columns of MTI table will be populated with the cause for the error.

PROCESS_FLAG
1 = Pending
2 = Running
3 = Error

How to Resubmit Unprocessed Transactions Stuck in the MTL_TRANSACTIONS_INTERFACE Table:
For example, if record fails to process due to error 'No open period found for date entered'. Now, we open Inventory Period and then update this PROCESS_FLAG by below steps:
1.  Navigate to Inventory Responsibility -> Transactions -> Transaction Open Interface
2.  Select the stuck line by click on check box and click on 'Save' icon.
3.  Now, PROCESS_FLAG will set to 1 and records is available to be pick by INCTCM.

MTL_MATERIAL_TRANSACTIONS_TEMP table

The transactions are passed to the MTL_MATERIAL_TRANSACTONS_TEMP table after initial validation by the Transactions Manager (INCTCM) from the MTL_TRANSACTIONS_INTERFACE. A job id is attached and a Transactions Worker (INCTCW) is submitted by the INCTCM process to get the records processed and moved to the MTL_MATERIAL_TRANSACTONS table.

This table is also used by Inventory Module and Purchasing module which writes directly onto this table for any transactions entered within itself and each transaction in turn through a process of strict validation.

This form queries the MTL_MATERIAL_TRANSACTIONS_TEMP table from front-end:

       Navigate Inventory Responsibility -> Transactions -> Pending Transactions

How to Resubmit Unprocessed Transactions Stuck in the MTL_MATERIAL_TRANSACTION_TEMP Table:
To resubmit all Pending Transactions from the Applications:
Navigation Inventory>Transactions>Pending Transactions>Tools>Submit All
This will choose all records for resubmission.
Click the Save icon.

Where do XML Publisher output files are stored in APPS?

How do I get the Output file of Concurrent Program generated from the server?

The concurrent manager first looks for the environment variable $APPLCSF. If this is set, it creates a path using two other environment variables: $APPLLOG and $APPLOUT. 

It places log files in $APPLCSF/$APPLLOG and output files go in $APPLCSF/$APPLOUT.

So for example, if you have set environment as: 
$APPLCSF = /u01/appl/common 
$APPLLOG = log 
$APPLOUT = out 
The concurrent manager will place log files in /u01/appl/common/log, and 
output files in /u01/appl/common/out 

Note: $APPLCSF must be a full, absolute path, and the other two are 
directory names. 

If $APPLCSF is not set, it places the files under the product top of the application associated with the request. So for example, a PO report would go under $PO_TOP/$APPLLOG and $PO_TOP/$APPLOUT 
Logfiles go to: /u01/appl/po/9.0/log 
Output files to: /u01/appl/po/9.0/out 
Of course, all these directories must exist and have the correct permissions. 


Note(1):
You can get the output file from following location:
-> cd $APPLCSF/$APPLOUT
-> ls -ltr *<request_id>*

where <request_id> has to be replaced by the report concurrent program request_id.

Note(2):

All concurrent requests produce a log file, but not necessarily an output file.

D2K Forms

Steps to Develop Custom Form in APPS

1. Copy TEMPLATE.fmb from $AU_TOP/forms/US, rename and open it on Forms Builder.
2. Click on the data block
3. Delete BLOCKNAME and DETAILBLOCK from it 
4. Now click on Windows.
5. Add new window.
6. Go to the properties of window (press F4)
7. Change subclass information (click on the property class radio button)
8. Now go to triggers
9. Click on the PRE-FORM
10. Change BLOCKNAME to i.e. Window name that you have defined and compile.
11. Click on the Program Units
12. Go to APP_CUSTOM (Package Body) and change to defined window then compile 
13. Now build DataBlocks and Canvas as desired.

Open "Oracle Form Builder":

File -> Open [Ctrl+O] and select 'TEMPLATE.fmb' :

File -> Save As "MY_FIRST_FORM.fmb".

Rename TEMPLATE (say MY_FORM) :

Now, select MY_FORM and press F4.
/* wrong step: Update 'Subclass Information' to WINDOW :

*/

Forms -> MY_FORM -> Triggers -> WHEN_NEW_FORM_INSTANCE -> F4 -> Trigger Text :
Replace TEMPLATE.fmb to MY_FORM.fmb

Select the 'Windows' and click on green-plus icon to create new window:

Now, rename it to XX_WIN.
Update 'Subclass Information' to WINDOW

In the same way, create a Canvas and rename it to XX_CANVAS.

now press F4 and set its properties :

Now, select

press F4 :

Replace "if (wnd = '<your first window>')" with "if (wnd = 'XX_WIN')" :

Now select 'Pre-Form':

Press F4 and update 'Trigger Text':
Replace "app_window.set_window_position('BLOCKNAME', 'FIRST_WINDOW');" with "app_window.set_window_position('XX_WIN', 'FIRST_WINDOW');"

Forms -> MY_FORM -> Data Blocks -> Click on 'Green +' icon -> Use the Data Block Wizard -> Next -> Table or View -> Select Table & Columns -> Next -> Data Block Name : EMP_XT -> Create the data block, then call the Layout Wizard -> Finish

Layout Wizard will appear:

Select the layout style for your frame by clicking a redio button below -> Form ->

To open again this "Layout" window, double click on XX_CANVAS:

Make sure, each item has subclass "TEXT_ITEM":

Login to putty.

Come to the path of .fmb file location.

cd /d01/oraapps/apps/apps_st/appl/au/12.0.0/forms/US

frmcmp_batch module=/d01/oraapps/apps/apps_st/appl/au/12.0.0/forms/US/MY_FIRST_FORM.fmb  userid=apps/summer13 output_file=/d01/oraapps/apps/apps_st/appl/mls/12.0.0/forms/US/MY_FIRST_FORM.fmx module_type=form batch=no compile_all=special

or from WinSCP:
Go to the path of .fmb file location.
In MNEU list of WinSCP: Commands -> Open Terminal (Ctrl + T)
Enter Command:
frmcmp_batch module=XXCNS_CFORM_LEGACY_DATA_UPDT.fmb userid=apps/t0mahawk5 output_file=$AR_TOP/forms/US/XXCNS_CFORM_LEGACY_DATA_UPDT.fmx module_type=form
Click on 'Execute' button.

Note: Here $AR_TOP is the TOP of application of 'FORM'.

View For Getting Customer Details

DROP VIEW apps.xx_cust_master_v;

CREATE OR replace VIEW apps.xx_cust_master_v (org_id, operting_unit, party_number, customer_class, account_number, party_name, address1, address2, address3, address4, city, postal_code, state, country, county, party_site_number, status, payment_term, party_site_name, cust_account_id, cust_acct_site_id, usage, pan_no, ec_code, price_list_name, tan_no, cst_reg_no, vat_reg_no, type_ibd_dom, branch, rsm, branch1, credit_limit, credit_check, contact_name, primary_phone_number email_id, phone, creation_date, last_update_date ) AS
SELECT DISTINCT hcasa.org_id, 
                hou.name operting_unit, 
                hp.party_number, 
                hca.customer_class_code customer_class, 
                hca.account_number, 
                hp.party_name, 
                hl.address1, 
                hl.address2, 
                hl.address3, 
                hl.address4, 
                hl.city, 
                hl.postal_code, 
                hl.state, 
                hl.country, 
                hl.county, 
                hps.party_site_number, 
                hps.status, 
                ( 
                       SELECT description 
                       FROM   apps.so_payment_terms_v 
                       WHERE  payment_term_id = hca.payment_term_id 
                       AND    ROWNUM = 1) payment_term, 
                hps.party_site_name, 
                hcasa.cust_account_id, 
                hcasa.cust_acct_site_id, 
                ter.site_use_code usage, 
                --bill_to_flag, 
                jcca.pan_no, 
                jcca.ec_code, 
                ( 
                       SELECT name 
                       FROM   apps.qp_list_headers 
                       WHERE  list_header_id = jcca.price_list_id) price_list_name, 
                jcca.tan_no, 
                jcca.cst_reg_no, 
                jcca.vat_reg_no, 
                ter.type_ibd_dom, 
                ter.branch, 
                ter.rsm, 
                ter.branch1, 
                hcpa.overall_credit_limit credit_limit, 
                hcp.credit_checking       credit_check, 
                ( 
                       SELECT hp1.party_name --CUSTOMER ACCOUNT CONTACT INFO 
                       FROM   apps.hz_cust_account_roles hcar , 
                              apps.hz_parties hp1 , 
                              apps.hz_relationships hr 
                       WHERE  1 = 1 
                       AND    hr.object_id = hp.party_id 
                       AND    hr.subject_id = hp1.party_id 
                       AND    hcar.party_id = hr.party_id 
                       AND    hcar.cust_acct_site_id IS NULL 
                       AND    hcar.status LIKE 'A' ) contact_name, 
                hp.primary_phone_number              primary_phone_number, 
                ( 
                       SELECT email_address 
                       FROM   apps.hz_contact_points 
                       WHERE  owner_table_id = hp.party_id 
                       AND    contact_point_type = 'EMAIL' 
                       AND    owner_table_name = 'HZ_PARTIES' 
                       AND    primary_flag = 'Y' 
                       AND    ROWNUM = 1) email_id, 
                ( 
                       SELECT phone_number 
                       FROM   apps.hz_contact_points 
                       WHERE  owner_table_id = hp.party_id 
                       AND    contact_point_type = 'PHONE' 
                       AND    owner_table_name = 'HZ_PARTIES' 
                       AND    primary_flag = 'Y' 
                       AND    ROWNUM = 1) phone, 
                hp.creation_date, 
                hcasa.last_update_date 
                --,ter.branch1 
FROM            apps.hz_locations hl, 
                apps.hz_party_sites hps, 
                apps.hz_cust_acct_sites_all hcasa, 
                apps.hz_parties hp, 
                apps.hz_cust_accounts hca, 
                apps.hr_operating_units hou, 
                apps.jai_cmn_cus_addresses jcca, 
                apps.hz_customer_profiles hcp, 
                apps.hz_cust_profile_amts hcpa, 
                ( 
                       SELECT org_id, 
                              cust_acct_site_id, 
                              rt.segment1 type_ibd_dom, 
                              rt.segment2 branch, 
                              rt.segment3 rsm, 
                              rt.segment4 branch1, 
                              hcu.site_use_code 
                       FROM   apps.hz_cust_site_uses_all hcu, 
                              apps.ra_territories rt 
                       WHERE  rt.territory_id(+) = hcu.territory_id --and hcu.org_id in(103,105,114,134,106,195,578,395,396,397) 
                ) ter 
WHERE           hcasa.party_site_id = hps.party_site_id 
AND             hps.location_id = hl.location_id 
AND             hp.party_id = hca.party_id 
AND             hp.party_id = hps.party_id 
AND             hca.cust_account_id = hcasa.cust_account_id 
AND             hcasa.org_id = hou.organization_id 
AND             hcasa.cust_account_id = jcca.customer_id(+) 
AND             hcasa.cust_acct_site_id = jcca.address_id(+) 
AND             hcasa.cust_acct_site_id = ter.cust_acct_site_id 
AND             hcp.cust_account_profile_id = hcpa.cust_account_profile_id(+) 
AND             hp.party_id = hcp.party_id(+) 
AND             hca.cust_account_id = hcp.cust_account_id 
AND             hcasa.org_id = ter.org_id 
AND             hp.party_type = 'ORGANIZATION' 
AND             hps.status = 'A' 
AND             hcasa.status = 'A' 
AND             hp.status = 'A' 
AND             hca.status = 'A' 
                --And hcasa.bill_to_flag in('P','Y') 
                --and hcasa.org_id = nvl(:p_organiztion_id,hcasa.org_id) 
                --and trunc(hp.CREATION_DATE) between trunc(:p_form_creation_date) and trunc(:p_to_creation_date) 
                --and trunc(hcasa.LAST_UPDATE_DATE) between trunc(:p_form_modified_date) and trunc(:p_to_modified_date)
                --and nvl(ter.branch1,'X')=nvl(:p_branch,nvl(ter.branch1,'X')) 
ORDER BY        1, 
                to_number (hp.party_number), 
                hp.party_name, 
                hca.account_number;