Wednesday 22 June 2011

Open Purchase Order uploading from legacy system to Oracle Apps

In order to upload open sales order following steps need to be performed:

1. Create a staging table as below.

2. Populate the staging table from user provided data in excel sheet.

3. Update the null field values based on the data provided as shown below.

4. Populate the data from the staging table into the Interface tables "PO_HEADERS_INTERFACE", "PO_LINES_INTERFACE" and "PO_DISTRIBUTIONS_INTERFACE" as shown below.

5. Run the "Import Standard Purchase Orders" request from the front end Apps Screen.

/********* CREATE SUPPLIER STAGING TABLE **********/

CREATE TABLE XX_OPEN_PO
(
SL_NO NUMBER,
VEND_CODE VARCHAR2(20 BYTE),
SUPPLIER VARCHAR2(50 BYTE),
SUPPLIER_SITE VARCHAR2(50 BYTE),
DESCRIPTION VARCHAR2(100 BYTE),
SHIP_TO_LOC VARCHAR2(50 BYTE),
BILL_TO_LOC VARCHAR2(50 BYTE),
ICODE VARCHAR2(50 BYTE),
ITEM VARCHAR2(50 BYTE),
QUANTITY NUMBER,
UOM VARCHAR2(10 BYTE),
UOM_CODE VARCHAR2(4 BYTE),
PRICE NUMBER,
CURRENCY VARCHAR2(4 BYTE),
NEED_BY_DATE DATE,
ORGANIZATION_NAME VARCHAR2(50 BYTE),
VERIFY_FLAG VARCHAR2(10 BYTE),
HEADER_ID NUMBER(10),
LINE_TYPE VARCHAR2(100 BYTE),
LINE_NUMBER NUMBER(12),
CHARGE_ACCOUNT VARCHAR2(50 BYTE),
MATCH_APPROVAL VARCHAR2(50 BYTE),
TAX VARCHAR2(50 BYTE),
CCID NUMBER,
VENDOR_ID NUMBER,
SHIP_TOLOC_ID NUMBER,
SHIP_TO_ORGID NUMBER
)


/******** GET AND UPDATE THE VENDOR_ID, SUPPLIER, AND SUPPLIER_SITE FIELDS STAGING TABLE *******/

update XX_open_po m set vendor_id = (select vendor_id from po_vendors
where upper(trim(vendor_name)) = upper(trim(m.supplier)))

update XX_open_po m set supplier = (select vendor_name from po_vendors
where upper(trim(vendor_name)) = upper(trim(m.supplier))) where m.VENDOR_ID is not null

update XX_open_po m set supplier_site =
(select a.vendor_site_code from
(SELECT MIN(vendor_site_id), vendor_site_code, vendor_id
FROM po_vendor_sites_all
GROUP BY vendor_site_id, vendor_site_code, vendor_id)a
where a.vendor_id = m.VENDOR_ID) where m.SUPPLIER_SITE is null


/***** INSERTING DATA INTO PO_HEADERS_INTERFACE *****/

TRUNCATE TABLE po.po_headers_interface

INSERT INTO po.po_headers_interface
(interface_header_id,
comments,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_id,
vendor_name,
vendor_site_code,
ship_to_location_id,
bill_to_location_id,
effective_date,
reference_num,
last_update_date
)
SELECT apps.po_headers_interface_s.NEXTVAL,
DESCRIPTION,
'PENDING',
'ORIGINAL',
81,-- l_org_id,
'STANDARD',
'INR',
84,
VEND_ID,
SUPPLIER,
SITE,
SHIPLOC_ID,
787,
TRUNC(TO_DATE('31-MAR-2011')),
'PO'||apps.po_headers_interface_s.NEXTVAL,
SYSDATE
FROM
(SELECT DISTINCT M.DESCRIPTION DESCRIPTION,
M.VENDOR_ID VEND_ID ,
M.supplier SUPPLIER,
M.SUPPLIER_SITE SITE,
M.SHIP_TOLOC_ID SHIPLOC_ID
FROM
XX_OPEN_PO M where verify_flag IS NULL)


/****** INSERTING DATA INTO PO_LINES_INTERFACE ******/

TRUNCATE TABLE po.po_lines_interface

INSERT INTO po.po_lines_interface
(interface_header_id,
interface_line_id,
line_num,
shipment_num,
line_type,
item,
item_description,
uom_code,
quantity,
unit_price,
organization_id,
need_by_date,
ship_to_organization_id,
ship_to_location
)
SELECT
P.INTERFACE_HEADER_ID,
APPS.PO_LINES_INTERFACE_S.NEXTVAL,
M.LINE_NUMBER,
M.LINE_NUMBER,
'Goods',
M.ICODE,
M.ITEM,
M.UOM_CODE,
M.QUANTITY,
M.PRICE,
M.SHIP_TO_ORGID,
TRUNC(TO_DATE('31-MAR-2011')),
M.SHIP_TO_ORGID,
M.SHIP_TO_LOC
FROM
XX_OPEN_PO M,po_headers_interface P
WHERE P.COMMENTS = M.DESCRIPTION

/******* INSERTING DATA INTO PO_DISTRIBUTIONS_INTERFACE ******/

TRUNCATE TABLE po.po_DISTRIBUTIONS_interface

INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered
)
-- CHARGE_ACCOUNT_ID)
SELECT L.INTERFACE_HEADER_ID,
L.INTERFACE_LINE_ID,
po.po_distributions_interface_s.NEXTVAL,
L.SHIPMENT_NUM,
M.QUANTITY
FROM
XX_OPEN_PO M,po_headers_interface P, PO_LINES_INTERFACE L
WHERE
P.COMMENTS = M.DESCRIPTION AND P.INTERFACE_HEADER_ID = L.INTERFACE_HEADER_ID
AND (M.LINE_NUMBER = L.LINE_NUM AND M.LINE_NUMBER = L.SHIPMENT_NUM)


/******* TRUNCATE TABLE PO_INTERFACE_ERRORS ********/

truncate table po.po_interface_errors

/**** AFTER COMPLETION OF THE UPLOADING PROCESS UPDATE VERIFY_FLAG OF STAGING TABLE *****/

UPDATE XX_OPEN_PO M SET VERIFY_FLAG = 'N' WHERE
M.DESCRIPTION IN (SELECT COMMENTS FROM po_headers_interface h
where h.PROCESS_CODE = 'REJECTED')
Posted by at 1 comment:

Monday 20 June 2011

Open Sales Order Upload from Legacy system to Oracle Apps

In order to upload open sales order following steps need to be performed:

1. Create a staging table as below.

2. Populate the staging table from user provided data in excel sheet.

3. Update the null field values based on the data provided as shown below.

4. Populate the data from the staging table into the Interface tables "OE_HEADERS_IFACE_ALL" and "OE_LINES_IFACE_ALL" as shown below.

5. Run the order Import Request from the front end Apps Screen.

/**************** Create a Staging Table *******************/
CREATE TABLE XX_SO_UPL
(
LEGACY_SO_NUMBER VARCHAR2(100 BYTE),
ORDER_DATE DATE,
CUSTOMER_NO VARCHAR2(20 BYTE),
CUSTOMER_ID NUMBER,
LINE_NUM NUMBER,
ICODE VARCHAR2(20 BYTE),
ITEM VARCHAR2(100 BYTE),
UOM VARCHAR2(10 BYTE),
CURRENCY VARCHAR2(5 BYTE),
QUANTITY NUMBER,
ORGANIZATION_NAME VARCHAR2(100 BYTE),
PRICE_UNIT NUMBER,
PRICE_LIST_ID NUMBER,
PAYMENT_TERMS VARCHAR2(100 BYTE),
SCHEDULED_SHIP_DATE DATE,
REQUEST_DATE DATE,
LINE_TYPE NUMBER,
INVENTORY_ITEM_ID NUMBER,
SHIP_FROM_ORGID NUMBER,
TERM_ID NUMBER,
ORDER_TYPE_ID NUMBER,
SOLD_TO_ORG_ID NUMBER,
SHIP_TO_ORG_ID NUMBER,
BILL_TO_ORG_ID NUMBER
)

/************** Get Ship_to_Org_id, Invoince_to_Org_id(Bill_to_org_id) **************/

UPDATE XX_SO_UPL M SET M.SHIP_TO_ORG_ID = (
SELECT K.SITE_USE_ID FROM
(
SELECT * FROM
(select MIN(hu.SITE_USE_ID) SITE_USE_ID, S.CUSTOMER_NO CUST_NO
from HZ_CUST_SITE_USES_ALL hu,
HZ_CUST_ACCT_SITES_ALL hs,
hz_cust_accounts_all hc, XX_so_upl S
Where hu.CUST_ACCT_SITE_ID = hs.CUST_ACCT_SITE_ID and hs.CUST_ACCOUNT_ID = hc.CUST_ACCOUNT_ID
and hc.ACCOUNT_NUMBER = S.CUSTOMER_NO and hu.SITE_USE_CODE = 'SHIP_TO' and s.SHIP_TO_ORG_ID is null
GROUP BY hu.SITE_USE_ID, S.CUSTOMER_NO
HAVING COUNT(hu.SITE_USE_ID) = 1
UNION ALL
select MIN(hu.SITE_USE_ID) SITE_USE_ID, S.CUSTOMER_NO CUST_NO
from HZ_CUST_SITE_USES_ALL hu,
HZ_CUST_ACCT_SITES_ALL hs,
hz_cust_accounts_all hc, XX_so_upl S
Where hu.CUST_ACCT_SITE_ID = hs.CUST_ACCT_SITE_ID and hs.CUST_ACCOUNT_ID = hc.CUST_ACCOUNT_ID
and hc.ACCOUNT_NUMBER = S.CUSTOMER_NO and hu.SITE_USE_CODE = 'SHIP_TO' and s.SHIP_TO_ORG_ID is null
GROUP BY hu.SITE_USE_ID, S.CUSTOMER_NO
HAVING COUNT(hu.SITE_USE_ID) > 1 )
) K
WHERE K.CUST_NO = M.CUSTOMER_NO) WHERE M.SHIP_TO_ORG_ID IS NULL


UPDATE XX_SO_UPL M SET M.BILL_TO_ORG_ID = (
SELECT K.SITE_USE_ID FROM
(
SELECT * FROM
(select MIN(hu.SITE_USE_ID) SITE_USE_ID, S.CUSTOMER_NO CUST_NO
from HZ_CUST_SITE_USES_ALL hu,
HZ_CUST_ACCT_SITES_ALL hs,
hz_cust_accounts_all hc, XX_so_upl S
Where hu.CUST_ACCT_SITE_ID = hs.CUST_ACCT_SITE_ID and hs.CUST_ACCOUNT_ID = hc.CUST_ACCOUNT_ID
and hc.ACCOUNT_NUMBER = S.CUSTOMER_NO and hu.SITE_USE_CODE = 'BILL_TO' and s.BILL_TO_ORG_ID is null
GROUP BY hu.SITE_USE_ID, S.CUSTOMER_NO
HAVING COUNT(hu.SITE_USE_ID) = 1
UNION ALL
select MIN(hu.SITE_USE_ID) SITE_USE_ID, S.CUSTOMER_NO CUST_NO
from HZ_CUST_SITE_USES_ALL hu,
HZ_CUST_ACCT_SITES_ALL hs,
hz_cust_accounts_all hc, XX_so_upl S
Where hu.CUST_ACCT_SITE_ID = hs.CUST_ACCT_SITE_ID and hs.CUST_ACCOUNT_ID = hc.CUST_ACCOUNT_ID
and hc.ACCOUNT_NUMBER = S.CUSTOMER_NO and hu.SITE_USE_CODE = 'BILL_TO' and s.BILL_TO_ORG_ID is null
GROUP BY hu.SITE_USE_ID, S.CUSTOMER_NO
HAVING COUNT(hu.SITE_USE_ID) > 1 )
) K
WHERE K.CUST_NO = M.CUSTOMER_NO) WHERE M.BILL_TO_ORG_ID IS NULL

/***************** Get order_type_id, line_type_id ****************************/

update XX_SO_UPL m set order_type_id =
(SELECT TRANSACTION_TYPE_ID
FROM OE_TRANSACTION_TYPES_TL
WHERE UPPER(NAME) = TRIM(UPPER(m.ORGANIZATION_NAME))) where m.order_type_id is null

UPDATE XX_SO_UPL M SET M.LINE_TYPE =
(
SELECT
A.DEFAULT_OUTBOUND_LINE_TYPE_ID
FROM OE_TRANSACTION_TYPES_ALL A
WHERE M.ORDER_TYPE_ID = A.TRANSACTION_TYPE_ID
) WHERE M.LINE_TYPE IS NULL

/***************** Get Ship_From_org_id, Term_id ****************************/

update XX_SO_UPL m set m.ship_FROM_ORGID =
(
SELECT o.ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS o
WHERE o.ORGANIZATION_NAME = m.ORGANIZATION_NAME) where m.ship_FROM_ORGID is null

UPDATE XX_SO_UPL S SET TERM_ID =
(SELECT TERM_ID
FROM RA_TERMS_TL
WHERE UPPER(trim(NAME)) = UPPER(TRIM(S.PAYMENT_TERMS))) WHERE S.TERM_ID IS NULL

/************* Get order source id******************/
SELECT ORDER_SOURCE_ID
FROM OE_ORDER_SOURCES
WHERE NAME = 'Online'

/**************** Inserting into Headers Interface table ************************/

truncate table ont.OE_HEADERS_IFACE_ALL

INSERT INTO OE_HEADERS_IFACE_ALL
( ORDER_SOURCE_ID
,ORIG_SYS_DOCUMENT_REF
,ORG_ID
-- ,SOLD_FROM_ORG_ID
,SHIP_FROM_ORG_ID
,ORDERED_DATE
,ORDER_TYPE_ID
,SOLD_TO_ORG_ID
,PAYMENT_TERM_ID
,OPERATION_CODE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CUSTOMER_PO_NUMBER
,PRICE_LIST_ID
,CONTEXT
,ATTRIBUTE19
,SHIP_TO_ORG_ID
,INVOICE_TO_ORG_ID
,customer_number
,salesrep_id
,order_category
,TRANSACTION_PHASE_CODE
,TRANSACTIONAL_CURR_CODE
,order_type
)
select
0
,OE_ORDER_HEADERS_S.NEXTVAL
,81
,ship_from
,ord_date
,ord_type
,sold_to_org_id
,term_id
,'INSERT'
,1110
,SYSDATE --CREATION_DATE
,1110 --LAST_UPDATED_BY
,SYSDATE --LAST_UPDATE_DATE
,CUST_PO
,PRICE_LIST
,NULL
,'LEGACY SO NUMBER'
,ship_to
,bill_to
,cust_no
,-3
,'ORDER'
,'F'
,CURR
,order_type
from
(select distinct
-- ,H.BILL_FROM_ORG_ID
H.SHIP_FROM_ORGID ship_from
,H.ORDER_DATE ord_date
,H.ORDER_TYPE_ID ord_type
,h.SOLD_TO_ORG_ID sold_to_org_id
,H.TERM_ID term_id
,H.LEGACY_SO_NUMBER CUST_PO
,H.SHIP_TO_ORG_ID ship_to
,H.BILL_TO_ORG_ID bill_to
,h.CUSTOMER_ID sold_to
,h.CUSTOMER_NO cust_no
,decode(h.currency,'USD',7085,'EUR',7092,'SEK',7093) PRICE_LIST
,H.CURRENCY CURR
,h.ORGANIZATION_NAME order_type
FROM XX_so_upl H
where h.CUSTOMER_ID <>0

/*****************Inserting into Lines Interface table ***********/

truncate table ont.OE_LINES_IFACE_ALL

INSERT INTO OE_LINES_IFACE_ALL L
(
ORDER_SOURCE_ID
,ORIG_SYS_DOCUMENT_REF
,ORIG_SYS_LINE_REF
,LINE_NUMBER
,INVENTORY_ITEM_ID
,ORDERED_QUANTITY
,ORG_ID
,PRICING_QUANTITY
,UNIT_SELLING_PRICE
,UNIT_LIST_PRICE
,PRICE_LIST_ID
,PAYMENT_TERM_ID
,SCHEDULE_SHIP_DATE
,REQUEST_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CALCULATE_PRICE_FLAG
,SOLD_TO_ORG_ID
,SHIP_FROM_ORG_ID
,SHIP_TO_ORG_ID
,INVOICE_TO_ORG_ID
,SALESREP_ID
,OPERATION_CODE
,LINE_TYPE_ID
)
SELECT
0
,o.ORIG_SYS_DOCUMENT_REF
,OE_ORDER_LINES_S.NEXTVAL
,L.LINE_NUM
,l.INVENTORY_ITEM_ID
,L.QUANTITY
,81
,L.QUANTITY
,L.PRICE_UNIT
,L.PRICE_UNIT
,decode(l.currency,'USD',7085,'EUR',7092,'SEK',7093) PRICE_LIST
,l.TERM_ID
,TO_DATE(L.ORDER_DATE,'DD-MON-RRRR')
,TO_DATE(L.ORDER_DATE,'DD-MON-RRRR')
,1110
,SYSDATE
,1110
,SYSDATE
,'N'
,L.SOLD_TO_ORG_ID
,L.SHIP_FROM_ORGID
,L.SHIP_TO_ORG_ID
,L.BILL_TO_ORG_ID
,-3
,'INSERT'
,L.LINE_TYPE
FROM
XX_SO_UPL L, OE_HEADERS_IFACE_ALL o
where l.LEGACY_SO_NUMBER=o.CUSTOMER_PO_NUMBER
and l.CUSTOMER_NO = o.CUSTOMER_NUMBER
and o.ORDER_TYPE_ID = l.ORDER_TYPE_ID
Posted by at No comments:

Saturday 18 June 2011

AR Receipt from a Customer and Refund to a different Customer R12.1.3

Scenario: Some times for subsidy industry Customer pays the subsidized amount. Then this money has to be forwarded to the corresponding GOVT organization against which GOVT will pay the full amount.

Refund money to same customer is very familiar but here money will be refunded to different customer (GOVT org).

Setup Required:

  1. Define a new Customer Account Hierarchical Relationship
  2. Attached both Customer with that Relationship
  3. Define “Refund” Receivable Activity

1. Customer Account Hierarchical Relationship Setup

Navigation: Oracle ReceivableàCustomeràRelationship Manager

Go to Setup tab. Then Click on Create

Put a meaningful Relationship name as you wish. Select “Yes. Circular relationships are not allowed” radio button. Then click next

Here you will see two segments one is ‘Subject’ another is ‘Object’.

Choose Subject Phrase = “Pay Form Of”, Subject Role Singular = PAY FROM CUSTOMER, Subject Role Plural = PAY FROM CUSTOMERS

Choose Object Phrase = “Pay To Of”, Object Role Singular = PAY TO CUSTOMER, Object Role Plural = PAY TO CUSTOMERS

Click Next then Finish.

Select ‘View details of this new relationship type’ then click on Go. Here click on Assign to Relationship Groups

Move “Pay within Group” and “Pay below Group” from Available Relationship Groups list to Assigned Relationship Groups for both ‘Pay To Of’ and ‘Pay From Of’ Relationship Phrase.

2. Attached both Customer with that Relationship

Now Create two Customer. Then query the parent Customer and Attach two Customers by Party Relationship.

Navigation: Query the Customer from Customer Master

Click Party Relationship. This relationship is different from customer relationship. Click on Add Another Row.

Search and select the Customer that you want to relate. Then search Relationship Role

Select Relationship Role as you defined earlier (Pay from Customer or Pay to Customer)

Save the record.

3. Define “Refund” Receivable Activity

Define Refund Activity

Give Name and Activity GL Account

Process Flow:

Lets we have relates Customer A and Customer B

Now create a receipt for Customer A and click on Apply button

In apply to field select Refund. Then click on Refund Attributes

In Customer name LOV you will find both Customer A and B. Here change the Customer (Select Customer B)

[ Here you may face some problem regarding this form. For this raise a SR to oracle. For my case Oracle suggest me to apply Patch 11791600. Though the patch includes other fixes the change is only in ARXRWAPP.fmb and ARXRWAPP.pld hence the change is in the receipt application form. This patch had solved my issue ]

In Customer Address field Change the Address

Select Refund Payment Method

Select Payment Term. Click on Apply. Save the Transaction

Click on ‘Refund Status’ to check the Payment Request and copy the Invoice Number

From Payables pay that invoice by Pay in Full concept by querying that Invoice.

Posted by at 2 comments:

Thursday 9 June 2011

How to download OUM 5.4 & 6.3(Oracle Unified Method) as AIM(Application Implementation Methodology) from where and how to use?

1.Clikc on the link bellow
  http://www.oracle.com/partners/en/knowledge-zone/applications/oracle-unified-method-359405.html

2.Click Implement Tab under Oracle Unified Method Knowledge Zone

" Note:
The OUM download file is an encrypted zip file, which requires a password. Please send an email opn-edu_ww@oracle.com to request the password to enable access to the encrypted OUM file.
Winzip 10.0 or higher is recommended for unzipping the OUM download file."

Click on the link bellow:-
OPN Member Oracle Unified Method 5.4 

3.Download the zip file from the link
4.Send the email to opn-edu_ww@oracle.com from your corporate email
5.Unzip the zip file with password received via email.
6. Install the application.

Now It is ready for use(Navigate from Window Start menu>Programs> Oracle Methods>Oracle OUM_E(Oracle Unified Method )5.4.0

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

For Download OUM(AIM) 6.3.0 version
  1. Go to the OPN Portal at partner.oracle.com.
  2. Select "Sign In / Register for Account".
  3. Sign In.
  4. From the Product Resources section, select "Applications".
  5. From the Applications page, locate and select the "Oracle Unified Method" link.
  6. From the Oracle Unified Method Knowledge Zone, locate the "I want to:" section.
  7. From the I want to: section, locate and select "Implement Solutions".
  8. From the Implement Solution page, locate the "Best Practices" section.
  9. From the Best Practices section, locate and select the "Download Oracle Unified Method (OUM)" link.
Posted by at 5 comments:

Tuesday 7 June 2011

How to change APPS login page logo(Oracle Logo) in R12.1.3

Navigate to  $OA_MEDIA top (Ex:- /u01/appl/apps_st/comn/java/classes/oracle/apps/media)

Replace the gif file(FNDSSCORP.gif) with your company logo in $OA_MEDIA folder.

Bounce the Apache
Clear all cookies in client machin
Test the senario.
Posted by at No comments:

Thursday 2 June 2011

APPS OPM(Oracle Process Manufacturing) Recipe Header Uploading through Script using API

Recipe Header Uploading can be done using the following steps:

1. First Create a staging table say "MJIL_RCP_HDR_UPL_TBL" as shown below.

CREATE TABLE MJIL_RCP_HDR_UPL_TBL
(
RECIPE_ID NUMBER(15),
RECIPE_DESCRIPTION VARCHAR2(70 BYTE),
RECIPE_NO VARCHAR2(32 BYTE),
RECIPE_VERSION NUMBER(5),
USER_ID NUMBER(15),
USER_NAME VARCHAR2(70 BYTE),
OWNER_ORGN_CODE VARCHAR2(4 BYTE),
CREATION_ORGN_CODE VARCHAR2(4 BYTE),
OWNER_ORGANIZATION_ID NUMBER,
CREATION_ORGANIZATION_ID NUMBER,
FORMULA_ID NUMBER(15),
FORMULA_NO VARCHAR2(32 BYTE),
FORMULA_VERS NUMBER,
ROUTING_ID NUMBER,
ROUTING_NO VARCHAR2(32 BYTE),
ROUTING_VERS NUMBER(5),
PROJECT_ID NUMBER(15),
RECIPE_STATUS VARCHAR2(30 BYTE),
PLANNED_PROCESS_LOSS NUMBER,
TEXT_CODE NUMBER(10),
DELETE_MARK NUMBER(5),
CONTIGUOUS_IND NUMBER,
ENHANCED_PI_IND VARCHAR2(1 BYTE),
RECIPE_TYPE NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER(15),
LAST_UPDATED_BY NUMBER(15),
LAST_UPDATE_DATE DATE,
LAST_UPDATE_LOGIN NUMBER(15),
OWNER_ID NUMBER(15),
OWNER_LAB_TYPE VARCHAR2(4 BYTE),
CALCULATE_STEP_QUANTITY NUMBER(5),
FIXED_PROCESS_LOSS NUMBER,
FIXED_PROCESS_LOSS_UOM VARCHAR2(3 BYTE),
ATTRIBUTE_CATEGORY VARCHAR2(30 BYTE),
ATTRIBUTE1 VARCHAR2(240 BYTE),
ATTRIBUTE2 VARCHAR2(240 BYTE),
ATTRIBUTE3 VARCHAR2(240 BYTE),
ATTRIBUTE4 VARCHAR2(240 BYTE),
ATTRIBUTE5 VARCHAR2(240 BYTE),
ATTRIBUTE6 VARCHAR2(240 BYTE),
ATTRIBUTE7 VARCHAR2(240 BYTE),
ATTRIBUTE8 VARCHAR2(240 BYTE),
ATTRIBUTE9 VARCHAR2(240 BYTE),
ATTRIBUTE10 VARCHAR2(240 BYTE),
ATTRIBUTE11 VARCHAR2(240 BYTE),
ATTRIBUTE12 VARCHAR2(240 BYTE),
ATTRIBUTE13 VARCHAR2(240 BYTE),
ATTRIBUTE14 VARCHAR2(240 BYTE),
ATTRIBUTE15 VARCHAR2(240 BYTE),
ATTRIBUTE16 VARCHAR2(240 BYTE),
ATTRIBUTE17 VARCHAR2(240 BYTE),
ATTRIBUTE18 VARCHAR2(240 BYTE),
ATTRIBUTE19 VARCHAR2(240 BYTE),
ATTRIBUTE20 VARCHAR2(240 BYTE),
ATTRIBUTE21 VARCHAR2(240 BYTE),
ATTRIBUTE22 VARCHAR2(240 BYTE),
ATTRIBUTE23 VARCHAR2(240 BYTE),
ATTRIBUTE24 VARCHAR2(240 BYTE),
ATTRIBUTE25 VARCHAR2(240 BYTE),
ATTRIBUTE26 VARCHAR2(240 BYTE),
ATTRIBUTE27 VARCHAR2(240 BYTE),
ATTRIBUTE28 VARCHAR2(240 BYTE),
ATTRIBUTE29 VARCHAR2(240 BYTE),
ATTRIBUTE30 VARCHAR2(240 BYTE)
)


2. Next create a procedure using the script as shown below.

CREATE OR REPLACE PROCEDURE CONA_RECIPE_UPLOAD_PD (ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)
is
/******************************************************************************
NAME: APPS.CONA_RECIPE_UPLOAD_PD
PURPOSE: Recipe Header Uploading

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 5/16/2011 1. Puspendu Das Created this procedure.

NOTES:

Automatically available Auto Replace Keywords:
Object Name: APPS.CONA_RECIPE_UPLOAD_PD
Sysdate: 5/16/2011
Date and Time: 5/16/2011, 1:22:42 PM, and 5/16/2011 1:22:42 PM

******************************************************************************/

mjil_rcp_hdr_tbl gmd_recipe_header.recipe_tbl;
mjil_rcp_hdr_flex_tbl gmd_recipe_header.recipe_flex;
X_status VARCHAR2(1);
X_msg_cnt NUMBER;
X_msg_dat VARCHAR2(1000);
X_row NUMBER := 1;
l_user_id number:= 1114;
l_responsibility_id number :=22883;
l_out_index NUMBER ;
l_responsibility_app_id number;
cursor c1 is
select * from mjil_rcp_hdr_upl_tbl;

BEGIN
FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_responsibility_id,l_responsibility_app_id);
for i in c1
loop
mjil_rcp_hdr_tbl(X_row).recipe_no := i.recipe_no;
mjil_rcp_hdr_tbl(X_row).recipe_version := i.recipe_version;
mjil_rcp_hdr_tbl(X_row).recipe_description := i.recipe_description;
mjil_rcp_hdr_tbl(X_row).RECIPE_STATUS := i.RECIPE_STATUS;
mjil_rcp_hdr_tbl(X_row).RECIPE_TYPE := i.RECIPE_TYPE;
mjil_rcp_hdr_tbl(X_row).formula_no := i.formula_no;
mjil_rcp_hdr_tbl(X_row).formula_vers := i.formula_vers;
mjil_rcp_hdr_tbl(X_row).routing_no := i.routing_no;
mjil_rcp_hdr_tbl(X_row).routing_vers := i.routing_vers;

mjil_rcp_hdr_tbl(X_row).delete_mark := i.delete_mark;
mjil_rcp_hdr_tbl(X_row).creation_date := SYSDATE;
mjil_rcp_hdr_tbl(X_row).created_by := i.created_by;
mjil_rcp_hdr_tbl(X_row).last_updated_by := i.last_updated_by;
mjil_rcp_hdr_tbl(X_row).last_update_date := SYSDATE;
mjil_rcp_hdr_tbl(X_row).last_update_login := 1114;

mjil_rcp_hdr_tbl(X_row).user_name := i.user_name;
mjil_rcp_hdr_tbl(X_row).owner_orgn_code := i.owner_orgn_code;
mjil_rcp_hdr_tbl(X_row).OWNER_ORGANIZATION_ID := i.owner_organization_id;
mjil_rcp_hdr_tbl(X_row).creation_orgn_code := i.creation_orgn_code;
mjil_rcp_hdr_tbl(X_row).owner_id := i.owner_id;

mjil_rcp_hdr_flex_tbl(X_row).attribute1 := 'FLEX1';

X_row := X_row+1;

end loop;
gmd_recipe_header.create_recipe_header(p_api_version => 1,
p_init_msg_list => FND_API.G_TRUE ,
p_commit => FND_API.G_TRUE,
p_called_from_forms => 'NO',
x_return_status => X_status,
x_msg_count => X_msg_cnt,
x_msg_data => X_msg_dat,
p_recipe_header_tbl => mjil_rcp_hdr_tbl,
p_recipe_header_flex => mjil_rcp_hdr_flex_tbl);

dbms_output.put_line('Return status - '||X_status);
dbms_output.put_line('Message count - '||X_msg_cnt);
for i IN 1 .. X_msg_cnt
LOOP
FND_MSG_PUB.get(p_msg_index => i,
p_encoded => 'F',
p_data => X_msg_dat,
P_MSG_INDEX_OUT => l_out_index);
DBMS_OUTPUT.PUT_LINE('Message Text '||X_msg_dat);
END LOOP;

exception
when others then
dbms_output.put_line('Return status - '||X_status);
dbms_output.put_line('Message count - '||X_msg_cnt);

for i IN 1 .. X_msg_cnt
LOOP
FND_MSG_PUB.get(p_msg_index => i,
p_encoded => 'F',
p_data => X_msg_dat,
P_MSG_INDEX_OUT => l_out_index);
DBMS_OUTPUT.PUT_LINE('Message Text '||X_msg_dat);
END LOOP;

END;
/

3. Finally register the procedure and run it.
Posted by at 1 comment:

APPS OPM(Oracle Process Manufacturing) Formula Uploading through Script using API

Formula Upload can be done using the following steps:

1. Create a staging table say "MJIL_FORMULA_UPLOAD" whose structure is given below.

CREATE TABLE MJIL_FORMULA_UPLOAD
(
RECORD_TYPE VARCHAR2(1 BYTE),
FORMULA_NO VARCHAR2(32 BYTE),
FORMULA_VERS NUMBER,
FORMULA_TYPE NUMBER,
FORMULA_DESC1 VARCHAR2(70 BYTE),
FORMULA_DESC2 VARCHAR2(70 BYTE),
FORMULA_CLASS VARCHAR2(32 BYTE),
FMCONTROL_CLASS VARCHAR2(32 BYTE),
INACTIVE_IND NUMBER,
OWNER_ORGANIZATION_ID NUMBER,
TOTAL_INPUT_QTY NUMBER,
TOTAL_OUTPUT_QTY NUMBER,
YIELD_UOM VARCHAR2(3 BYTE),
FORMULA_STATUS VARCHAR2(30 BYTE),
OWNER_ID NUMBER(15),
FORMULA_ID NUMBER,
FORMULALINE_ID NUMBER,
LINE_TYPE NUMBER,
LINE_NO NUMBER,
ITEM_NO VARCHAR2(2000 BYTE),
INVENTORY_ITEM_ID NUMBER,
REVISION VARCHAR2(3 BYTE),
QTY NUMBER,
DETAIL_UOM VARCHAR2(3 BYTE),
MASTER_FORMULA_ID NUMBER,
RELEASE_TYPE NUMBER,
SCRAP_FACTOR NUMBER,
SCALE_TYPE_HDR NUMBER,
SCALE_TYPE_DTL NUMBER,
COST_ALLOC NUMBER,
PHANTOM_TYPE NUMBER,
REWORK_TYPE NUMBER,
BUFFER_IND NUMBER,
BY_PRODUCT_TYPE VARCHAR2(1 BYTE),
INGREDIENT_END_DATE DATE,
ATTRIBUTE1 VARCHAR2(240 BYTE),
ATTRIBUTE2 VARCHAR2(240 BYTE),
ATTRIBUTE3 VARCHAR2(240 BYTE),
ATTRIBUTE4 VARCHAR2(240 BYTE),
ATTRIBUTE5 VARCHAR2(240 BYTE),
ATTRIBUTE6 VARCHAR2(240 BYTE),
ATTRIBUTE7 VARCHAR2(240 BYTE),
ATTRIBUTE8 VARCHAR2(240 BYTE),
ATTRIBUTE9 VARCHAR2(240 BYTE),
ATTRIBUTE10 VARCHAR2(240 BYTE),
ATTRIBUTE11 VARCHAR2(240 BYTE),
ATTRIBUTE12 VARCHAR2(240 BYTE),
ATTRIBUTE13 VARCHAR2(240 BYTE),
ATTRIBUTE14 VARCHAR2(240 BYTE),
ATTRIBUTE15 VARCHAR2(240 BYTE),
ATTRIBUTE16 VARCHAR2(240 BYTE),
ATTRIBUTE17 VARCHAR2(240 BYTE),
ATTRIBUTE18 VARCHAR2(240 BYTE),
ATTRIBUTE19 VARCHAR2(240 BYTE),
ATTRIBUTE20 VARCHAR2(240 BYTE),
ATTRIBUTE21 VARCHAR2(240 BYTE),
ATTRIBUTE22 VARCHAR2(240 BYTE),
ATTRIBUTE23 VARCHAR2(240 BYTE),
ATTRIBUTE24 VARCHAR2(240 BYTE),
ATTRIBUTE25 VARCHAR2(240 BYTE),
ATTRIBUTE26 VARCHAR2(240 BYTE),
ATTRIBUTE27 VARCHAR2(240 BYTE),
ATTRIBUTE28 VARCHAR2(240 BYTE),
ATTRIBUTE29 VARCHAR2(240 BYTE),
ATTRIBUTE30 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE1 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE2 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE3 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE4 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE5 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE6 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE7 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE8 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE9 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE10 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE11 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE12 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE13 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE14 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE15 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE16 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE17 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE18 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE19 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE20 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE21 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE22 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE23 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE24 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE25 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE26 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE27 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE28 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE29 VARCHAR2(240 BYTE),
DTL_ATTRIBUTE30 VARCHAR2(240 BYTE),
ATTRIBUTE_CATEGORY VARCHAR2(30 BYTE),
DTL_ATTRIBUTE_CATEGORY VARCHAR2(30 BYTE),
TPFORMULA_ID NUMBER,
IAFORMULA_ID NUMBER,
SCALE_MULTIPLE NUMBER,
CONTRIBUTE_YIELD_IND VARCHAR2(1 BYTE),
SCALE_UOM VARCHAR2(4 BYTE),
CONTRIBUTE_STEP_QTY_IND VARCHAR2(1 BYTE),
SCALE_ROUNDING_VARIANCE NUMBER,
ROUNDING_DIRECTION NUMBER,
TEXT_CODE_HDR NUMBER,
TEXT_CODE_DTL NUMBER,
USER_ID NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER(15),
LAST_UPDATED_BY NUMBER(15),
LAST_UPDATE_DATE DATE,
LAST_UPDATE_LOGIN NUMBER(15),
USER_NAME VARCHAR2(100 BYTE),
DELETE_MARK NUMBER,
AUTO_PRODUCT_CALC VARCHAR2(1 BYTE),
PROD_PERCENT NUMBER
)

2. Next create a procedure similar to the one given below.

CREATE OR REPLACE PROCEDURE APPS.CONA_FML_UPLOAD_PD (ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)
is
/******************************************************************************
NAME: CONA_FML_UPLOAD_PD
PURPOSE: Formula Uploading

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 5/16/2011 1. Puspendu Das Created this procedure.

NOTES:

Automatically available Auto Replace Keywords:
Object Name: CONA_FML_UPLOAD_PD
Sysdate: 5/16/2011
Date and Time: 5/16/2011, 1:22:42 PM, and 5/16/2011 1:22:42 PM

******************************************************************************/
mjil_fml_tabtype apps.gmd_formula_pub.formula_insert_hdr_tbl_type;
cursor c1 is
select * from MJIL_FORMULA_UPLOAD where formula_no not in(select formula_no from fm_form_mst);
cnt number;
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(1000);
l_out_index NUMBER :=0;
l_user_id number:= 1114;
l_responsibility_id number :=22883;
l_responsibility_app_id number;
begin
FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_responsibility_id,l_responsibility_app_id);
cnt := 0;
for i in c1
loop
cnt := cnt+1;
mjil_fml_tabtype(cnt).record_type := i.record_type;
mjil_fml_tabtype(cnt).FORMULA_NO := trim(i.FORMULA_NO);
mjil_fml_tabtype(cnt).FORMULA_VERS := i.formula_vers;
mjil_fml_tabtype(cnt).formula_type := i.formula_type;
mjil_fml_tabtype(cnt).formula_desc1 := i.formula_desc1;
mjil_fml_tabtype(cnt).formula_desc2 := i.formula_desc2;
mjil_fml_tabtype(cnt).inactive_ind := i.inactive_ind;
mjil_fml_tabtype(cnt).OWNER_ORGANIZATION_ID := i.owner_organization_id;
mjil_fml_tabtype(cnt).total_input_qty := i.total_input_qty;
mjil_fml_tabtype(cnt).total_output_qty := i.total_output_qty;
mjil_fml_tabtype(cnt).formula_status := i.formula_status;
mjil_fml_tabtype(cnt).line_no := i.line_no;
mjil_fml_tabtype(cnt).line_type := i.line_type;
mjil_fml_tabtype(cnt).item_no := i.item_no;
mjil_fml_tabtype(cnt).qty := i.qty;
mjil_fml_tabtype(cnt).detail_uom := i.detail_uom;
mjil_fml_tabtype(cnt).release_type := i.release_Type;
mjil_fml_tabtype(cnt).scrap_factor := i.scrap_factor;
mjil_fml_tabtype(cnt).scale_type_hdr := i.scale_type_hdr;
mjil_fml_tabtype(cnt).scale_type_dtl := i.scale_type_dtl;
mjil_fml_tabtype(cnt).cost_alloc := i.cost_alloc ;
mjil_fml_tabtype(cnt).phantom_type := i.phantom_type;
mjil_fml_tabtype(cnt).rework_type := i.rework_type ;
mjil_fml_tabtype(cnt).buffer_ind := i.buffer_ind ;
mjil_fml_tabtype(cnt).contribute_yield_ind := i.contribute_yield_ind ;
mjil_fml_tabtype(cnt).contribute_step_qty_ind := i.contribute_step_qty_ind ;
mjil_fml_tabtype(cnt).delete_mark := i.delete_mark;
end loop;
GMD_FORMULA_PUB.Insert_Formula
( p_api_version => 1
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_TRUE
, p_called_from_forms => 'NO'
, x_return_status => l_return_status
, x_msg_count =>l_msg_count
, x_msg_data =>l_msg_data
, p_formula_header_tbl => mjil_fml_tabtype);
dbms_output.put_line('Return status - '||l_return_status);
dbms_output.put_line('Message count - '||l_msg_count);
for i IN 1 .. l_msg_count
LOOP
FND_MSG_PUB.get(p_msg_index => i,
p_encoded => 'F',
p_data => l_msg_data,
P_MSG_INDEX_OUT => l_out_index);
DBMS_OUTPUT.PUT_LINE('Message Text '||l_msg_data);
END LOOP;
exception
when others then
dbms_output.put_line('Return status - '||l_return_status);
dbms_output.put_line('Message count - '||l_msg_count);

for i IN 1 .. l_msg_count
LOOP
FND_MSG_PUB.get(p_msg_index => i,
p_encoded => 'F',
p_data => l_msg_data,
P_MSG_INDEX_OUT => l_out_index);
DBMS_OUTPUT.PUT_LINE('Message Text '||l_msg_data);
END LOOP;

END;

3. Register and run the procedure in Oracle Apps.
Posted by at 1 comment:

Wednesday 1 June 2011

Oracle APPS Miscellaneous receipt Transaction / openning stock upload through interface sample script

   We need to create a customer table :-

CREATE TABLE XX_STOCK
     (  sl_no                       number,
        organization_name   varchar2(30),
        organization_id        number,
        item                         varchar2(20),
        item_id                    number,
        uom                         varchar2(3),
        subinventory            varchar2(10),
        quantity                   number,
        lot                           varchar2(30),
        txn_date                  date,
        ucost                       number
     );

We need to populate customer table with proper data. Then we need to insert all the data in interface tables. If items are lot enabled we need also to populate lot interface data.

INSERT INTO MTL_TRANSACTIONS_INTERFACE 
   ( process_flag                   ,
     validation_required         ,  
     transaction_mode           , 
     lock_flag                        ,     
     last_update_date            ,     
     last_updated_by             ,  
     creation_date                 ,        
     created_by                     ,
     inventory_item_id           ,    
     organization_id               ,  
     transaction_quantity        , 
     primary_quantity             ,           
     transaction_uom             , 
     transaction_date             ,     
     subinventory_code         ,
     transaction_action_id      ,
     transaction_type_id        ,  
     transaction_interface_id  ,
     source_code                  ,
     source_line_id                ,
     source_header_id           ,
     distribution_account_id   ,
     transaction_cost      )
   select
     1                          ,
     1                          ,
     3                          ,
     2                          ,
     txn_date               ,
     1114                    ,
     txn_date               ,
     1114                    ,
     item_id                 ,
     organization_id     ,
     quantity                ,
     quantity                ,
     UOM                   ,
     txn_date               ,
     subinventory         ,
     27                        ,
     42                        ,
     sl_no                    ,
     'Stock_upload'     ,
     sl_no                    ,
     -1                         ,
     3723                    ,
     ucost
   FROM XX_STOCK;

   INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE           ( TRANSACTION_INTERFACE_ID                   ,
      LAST_UPDATE_DATE                                  ,
      LAST_UPDATED_BY                                    ,
      CREATION_DATE                                         ,
      CREATED_BY                                                ,
      LOT_NUMBER                                               ,
      TRANSACTION_QUANTITY                       ,
      PROCESS_FLAG                                            ,
      SOURCE_CODE                                            ,
      SOURCE_LINE_ID                          
    )
   SELECT
      sl_no                                  ,
      txn_date                             ,
      1114                                  ,
      txn_date                             ,
      1114                                  ,
      lot                                      ,
      quantity                              ,
      1                                        , 
      'Stock_upload'                   ,
      sl_no                                  
    FROM XX_STOCK;

Launch Material Transaction from Inventory Interface Manager.
N:- Inventory>Setup>Transaction>Interface Managers
It will launch 'Process transaction interface' and 'Process transaction interface' will call 'Inventory transaction worker'
Posted by at 1 comment:
Subscribe to: Posts (Atom)