GL INTERFACE CODE
CREATE OR REPLACE PROCEDURE GE_Pro(Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2) IS
-- cursor declaration
CURSOR gl_cur IS
SELECT
status ,
set_of_books_id ,
accounting_date ,
currency ,
date_created ,
created_by ,
actual_flag ,
category ,
source ,
curr_conversion ,
segment1 ,
segment2 ,
segment3 ,
segment4 ,
segment5 ,
entered_dr ,
entered_cr ,
accounted_dr ,
accounted_cr ,
group_id
FROM VENKAT_GL_INBOUND_INTERFACE;
l_currencycode VARCHAR2(25);
l_set_of_books_id NUMBER(3);
l_flag VARCHAR2(2);
l_error_msg VARCHAR2(100);
l_err_flag VARCHAR2(10);
l_category VARCHAR2(100);
L_USERID NUMBER(10);
BEGIN
DELETE FROM gl_interface;
COMMIT;
FOR rec_cur IN gl_cur LOOP
l_flag:='A';
l_err_flag:='A';
--This PL/SQL Block will do the currency validation
--end of the currency validation
--Category Column Validation
BEGIN
SELECT USER_JE_CATEGORY_NAME
INTO l_CATEGORY
FROM GL_JE_CATEGORIES
WHERE USER_JE_CATEGORY_NAME = REC_CUR.Category;
EXCEPTION
WHEN OTHERS THEN
l_category:=NULL;
l_flag:='E';
l_error_msg:='Category does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
--End Category Column Validation
--User ID column validation
BEGIN
SELECT USER_ID
INTO L_USERID
FROM FND_USER
WHERE USER_ID = REC_CUR.created_by;
EXCEPTION
WHEN OTHERS THEN
L_userid:=NULL;
l_flag:='E';
l_error_msg:='User ID does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
--End of Created_by OR UserID column Validation
--Set of books Validation
BEGIN
SELECT set_of_books_id
INTO l_set_of_books_id
FROM GL_SETS_OF_BOOKS
WHERE set_of_books_id=rec_cur.set_of_books_id;
EXCEPTION
WHEN OTHERS THEN
l_set_of_books_id:=NULL;
l_flag:='E';
l_error_msg:='set of Books ID does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
-- End Set of books Validation
--Status Column validation
/* BEGIN
IF rec_cur.status = 'NEW' THEN
l_flag:= 'A';
ELSE
l_flag:= 'E';
Fnd_File.put_line (Fnd_File.LOG,'Status column has got invalid data');
END IF;
END;*/
BEGIN
SELECT currency_code
INTO l_currencycode
FROM fnd_currencies
WHERE currency_code=rec_cur.currency
AND currency_code='USD';
EXCEPTION
WHEN OTHERS THEN
l_currencycode:=NULL;
l_flag:='E';
l_error_msg:='currency code does not exists';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
--End of Actual Flag Column validation
IF l_flag!='E' THEN
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE');
INSERT INTO gl_interface(status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
user_currency_conversion_type,
segment1,
segment2,
segment3,
segment4,
segment5,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
group_id)
VALUES
(rec_cur.status ,
rec_cur.set_of_books_id ,
rec_cur.accounting_date ,
rec_cur.currency ,
rec_cur.date_created ,
rec_cur.created_by ,
rec_cur.actual_flag ,
rec_cur.category ,
rec_cur.source ,
rec_cur.curr_conversion ,
rec_cur.segment1 ,
rec_cur.segment2 ,
rec_cur.segment3 ,
rec_cur.segment4 ,
rec_cur.segment5 ,
rec_cur.entered_dr ,
rec_cur.entered_cr ,
rec_cur.accounted_dr ,
rec_cur.accounted_cr ,
rec_cur.group_id);
END IF;
l_flag:=NULL;
l_error_msg:=NULL;
END LOOP;
COMMIT;
END GE_Pro;
/
Tuesday, 19 April 2011
GL INTERFACE CODE
OUTBOUND INTERFACE(INVENTORY)
OUTBOUND INTERFACE(INVENTORY)
CREATE OR REPLACE procedure RAMANA_INV_Out1(Errbuf OUT varchar2,
Retcode ouT varchar2,
f_id in number,
t_id in varchar2) as
cursor c1 is select
msi.segment1 item,
msi.inventory_item_id Itemid,
msi.description itemdesc,
msi.primary_uom_code Uom,
ood.organization_name name,
ood.organization_id id,
mc . segment1||','||mc.segment2 Category
from
mtl_system_items_b msi,
org_organization_definitions ood,
mtl_item_categories mic,
mtl_categories mc
where
msi.organization_id = ood.organization_id
and msi.inventory_item_id = mic.inventory_item_id
and msi.organization_id = mic.organization_id
and mic.category_id = mc.category_id
and msi.purchasing_item_flag = 'Y'
and msi.organization_id between f_id and t_id;
x_id utl_file.file_type;
l_count number(5) default 0;
begin
x_id:=utl_file.fopen('d:\oracle\proddb\8.1.7\plsql\temp','invoutdata.dat','W');
--select * from v$parameter where name like '%utl_file%'
for x1 in c1 loop
l_count:=l_count+1;
utl_file.put_line(x_id,x1.item ||'-'||
x1.itemid ||'-'||
x1.itemdesc||'-'||
x1.uom ||'-'||
x1.name ||'-'||
x1.id ||'-'||
x1.category );
end loop;
utl_file.fclose(x_id);
Fnd_file.Put_line(Fnd_file.output,'No of Records transfered to the data file :'||l_count);
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted User name '||Fnd_Profile.Value('USERNAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name '||Fnd_profile.value('RESP_NAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submission Date :'|| SYSDATE);
Exception
WHEN utl_file.invalid_operation THEN
fnd_file.put_line(fnd_File.log,'invalid operation');
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
fnd_file.put_line(fnd_File.log,'invalid path');
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
fnd_file.put_line(fnd_File.log,'invalid mode');
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
fnd_file.put_line(fnd_File.log,'invalid filehandle');
utl_file.fclose_all;
WHEN utl_file.read_error THEN
fnd_file.put_line(fnd_File.log,'read error');
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
fnd_file.put_line(fnd_File.log,'internal error');
utl_file.fclose_all;
WHEN OTHERS THEN
fnd_file.put_line(fnd_File.log,'other error');
utl_file.fclose_all;
End RAMANA_INV_Out1;
How to Set R12 Supplier Page in Read Only Mode?
How to Set R12 Supplier Page in Read Only Mode?
Open System Administrator resp. Navigate to Security->Responsibility->
o Query responsibility name(Ex: Payables, HG Operations) you want to use that will have the Supplier page as Read Only.
o Under Menu Exclusions select Type as 'Function' and Name as 'Supplier Full Access : Buyer View'
o Save changes.
Navigation Path: System Administrator->Security->
o Query responsibility name(Ex: Payables, HG Operations) you want to use that will have the Supplier page as Read Only.
o Under Menu Exclusions select Type as 'Function' and Name as 'Supplier Full Access : Buyer View'
o Save changes.
Navigation Path: System Administrator->Security->
o Now Now open Suppliers page and check changes open Suppliers page and check changes.
Navigation Path: Payables Resp(Payables, HG Operations)->Suppliers->EntrySupplier page looks like this in read only mode
Prepayment Invoices(AP)
Prepayment Invoices
A prepayment is a type of invoice you enter to make an advance payment to a supplier or employee. You can enter two types of prepayments: Temporary and Permanent.
Temporary prepayments can be applied to invoices or expense reports you receive. For example, you use a Temporary prepayment to pay a hotel a catering deposit. When the hotel's invoice arrives, apply the prepayment to the invoice to reduce the invoice amount you pay.
Permanent prepayments cannot be applied to invoices. For example, you use a Permanent prepayment to pay a lease deposit for which you do not expect to be invoiced.
Include Prepayment in Invoice:
You can enter supplier invoices where the invoice amount includes prepayments. If you receive a supplier invoice, and the invoice amount has been reduced because of prepayments that the supplier has received, you need to indicate that the invoice amount includes one or more prepayments.
Example:
Prepay Invoice: 200$
Standard Invoice: 500$
Case 1: If you apply Prepay on Standard invoice with 'Apply' check box checked in Apply/Unapply window
Result on Standard Invoice after apply Prepay
Header
Invoice Amount: 500$
Paid Amount : 200$
Distributions
Item 500$
Prepay -200$
In this case distribution total is 500 not 300 and amount paid is 200, remaining amount to pay is 300.
Case 2: If you apply Prepay on Standard invoice with both check boxes selected in Apply/Unapply window
Result on Standard Invoice after apply Prepay
Header
Invoice Amount: 300$
Paid Amount : 0$
Distributions
Item 500$
Prepay -200$ 'Prepayment on Invoice'-Checked
In this case distribution total is 300 because prepay is part of Std invoice. Amount to pay is 300.
Note: Because the Distribution Total includes prepayments, you need to ensure that you have applied prepayments before you submit the invoice for Invoice Validation.
Points To Note:
Temporary prepayments can be applied to invoices or expense reports you receive. For example, you use a Temporary prepayment to pay a hotel a catering deposit. When the hotel's invoice arrives, apply the prepayment to the invoice to reduce the invoice amount you pay.
Permanent prepayments cannot be applied to invoices. For example, you use a Permanent prepayment to pay a lease deposit for which you do not expect to be invoiced.
Include Prepayment in Invoice:
You can enter supplier invoices where the invoice amount includes prepayments. If you receive a supplier invoice, and the invoice amount has been reduced because of prepayments that the supplier has received, you need to indicate that the invoice amount includes one or more prepayments.
Example:
Prepay Invoice: 200$
Standard Invoice: 500$
Case 1: If you apply Prepay on Standard invoice with 'Apply' check box checked in Apply/Unapply window
Header
Invoice Amount: 500$
Paid Amount : 200$
Distributions
Item 500$
Prepay -200$
In this case distribution total is 500 not 300 and amount paid is 200, remaining amount to pay is 300.
Case 2: If you apply Prepay on Standard invoice with both check boxes selected in Apply/Unapply window
Header
Invoice Amount: 300$
Paid Amount : 0$
Distributions
Item 500$
Prepay -200$ 'Prepayment on Invoice'-Checked
In this case distribution total is 300 because prepay is part of Std invoice. Amount to pay is 300.
Note: Because the Distribution Total includes prepayments, you need to ensure that you have applied prepayments before you submit the invoice for Invoice Validation.
Points To Note:
- Settlement date: The date after which the prepayment can be applied to an invoice.
- On a prepayment, you can enter any number of distributions, either manually, or automatically by purchase order matching, distribution sets, or allocating.
- You can take discounts on prepayments.
- You can create Foreign Currency Prepayments.
- You cannot partially pay a prepayment. you must fully pay it.
- You can apply prepay on the invoice type is Standard, Mixed, or Expense Report.
- You can apply only Item distributions from the prepayment.
- You must fully pay a prepayment before you can apply the prepayment to an invoice.
- If you want to apply Permanent type prepay then you can change the Prepayment Type to Temporary.
- Supplier, invoice currency and payment currency must be same on prepay and to invoice.
- If the prepayment has a value in the Prepayment PO Number field, then the invoice must be matched to the same purchase order.
- GL Date on Apply/Unapply window, which is the accounting date for the new Prepayment distributions that Payables creates when you apply a prepayment. This date must be after the latest accounting date on any distribution for either prepayment or invoice and must be in an open period.
SUPPLIER AND SITE CREATION API
SUPPLIER CREATION APIDECLARE
l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_id NUMBER;
l_party_id NUMBER;
BEGIN
--Required
l_vendor_rec.segment1 := '123'; --ID
l_vendor_rec.vendor_name := 'ABCD'; --Supplier Name
--Optional
l_vendor_rec.match_option:='R' ;
pos_vendor_pub_pkg.create_vendor
(
p_vendor_rec => l_vendor_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_id => l_vendor_id,
x_party_id => l_party_id
);
COMMIT;
dbms_output.put_line('return_status: '||l_return_status);
dbms_output.put_line('msg_data: '||l_msg_data);
dbms_output.put_line('vendor_id: '||l_vendor_id);
dbms_output.put_line('party_id: '||l_party_id);
END;
##########################################################################
DECLARE
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_site_id NUMBER;
l_party_site_id NUMBER;
l_location_id NUMBER;
BEGIN
--Required
l_vendor_site_rec.vendor_id :='37172';
l_vendor_site_rec.vendor_site_code := 'xyz';
l_vendor_site_rec.address_line1 := 'A-602';
l_vendor_site_rec.country := 'IN';
l_vendor_site_rec.org_id := '80';
--Optional
l_vendor_site_rec.purchasing_site_flag:='Y';
l_vendor_site_rec.pay_site_flag :='Y';
l_vendor_site_rec.rfq_only_site_flag :='N';
l_vendor_site_rec.phone := '67615006';
pos_vendor_pub_pkg.create_vendor_site
(
p_vendor_site_rec => l_vendor_site_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_site_id => l_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id
);
COMMIT;
dbms_output.put_line('return_status: '||l_return_status);
dbms_output.put_line('msg_data: '||l_msg_data);
dbms_output.put_line('vendor_site_id: '||l_vendor_site_id);
dbms_output.put_line('party_site_id: '||l_party_site_id);
dbms_output.put_line('location_id: '||l_location_id);
END;SUPPLIER PHONE DETAILS QUERY
SUPPLIER PHONE DETAILS QUERYSELECT hp.party_id, hp.person_first_name, hp.person_last_name, hp.party_name, APS.VENDOR_NAME, hcpp.email_address, hcpp.phone_number FROM hz_parties hp, hz_contact_points hcpp, AP_SUPPLIERS APS WHERE HP.PARTY_ID=APS.PARTY_ID AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
INVENTORY QUERY
INVENTORY QUERYSELECT msi.segment1 AS item_code
, mp.organization_code AS org_code
, decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name) AS subinv_code
, sum(decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.transaction_quantity,sinv.secondary_inventory_name,moq.transaction_quantity,0))AS ohq
FROM
apps.mtl_system_items_b msi
, apps.mtl_onhand_quantities moq
, apps.mtl_parameters mp
, (SELECT msec.secondary_inventory_name,
msec.organization_id
FROM mtl_secondary_inventories msec
WHERE msec.secondary_inventory_name IN ('Sub_INVA','Sub_INVB')) sinv
WHERE AND msi.inventory_item_status_code = 'Active'
AND msi.inventory_item_id = moq.inventory_item_id(+)
AND msi.organization_id = moq.organization_id(+)
AND (moq.subinventory_code IN ('Sub_INVA','Sub_INVB') OR (moq.subinventory_code IS NULL AND sinv.secondary_inventory_name IN ('Sub_INVA','Sub_INVB')))
AND msi.organization_id = mp.organization_id
AND msi.organization_id = sinv.organization_id
GROUP BY msi.segment1,
mp.organization_code,
decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name)
ORDER BY msi.segment1,
decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name),
msi.segment1;
--------------------------------------------------------------------------------------------------------
SELECT hou.NAME org, mp.organization_code AS org_code,
decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name) AS subinventory,
mil.concatenated_segments LOCATOR, mil.segment1 warehouse_id,
mil.segment2 product_type, mil.segment3 container,
mil.segment4 aisle, mil.segment5 bay, mil.segment6 shelf,
mil.segment7 project, msi.segment1 AS item_code,
msi.description description, msi.primary_uom_code primary_uom,
sum(decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.transaction_quantity,sinv.secondary_inventory_name,moq.transaction_quantity,0)) AS ohq,
moq.lot_number lot_number, mln.expiration_date expire_date,
cic.item_cost standard_cost, msi.list_price_per_unit,
mil.attribute1 container_seal, fu1.user_name qty_created_by,
fu2.user_name qty_last_updated_by, msi.attribute1 rotatable,
msi.attribute2 slep_eligible, msi.attribute3 categoryi_v,
TO_NUMBER (msi.attribute4) "Case To Bottle",
TO_NUMBER (msi.attribute5) "Case To Each",
TO_NUMBER (msi.attribute6) "Pallet QTY",
mcst.category_set_name category_name,
mc.concatenated_segments item_category,
TO_DATE (SUBSTR (mil.attribute2, 1, 10), 'YYYY/MM/DD') seal_date,
mil.attribute4 seal_color, mil.attribute3 seal_by,
element10.element_value "Indications",
element20.element_value "Dose",
element30.element_value "Customer",
element40.element_value "Adult/Ped",
element50.element_value "Pregnancy Class",
element60.element_value "Product Identifier",
element70.element_value "Conv UOM", mln.attribute1 slep_status,
mc.segment1 agent_type, mc.segment2 administrator,
mc.segment3 drug_type, mc.segment4 drug_strength,
mcr1.cross_reference, mcr2.cross_reference, mcr3.cross_reference,
mcr4.cross_reference, misi.min_minmax_quantity,
misi.max_minmax_quantity, msi.min_minmax_quantity,
msi.max_minmax_quantity, mln.attribute2 original_expiration_date,
mln.attribute3 first_extension_project,
mln.attribute4 first_extension_date,
mln.attribute5 second_extension_project,
mln.attribute6 second_extension_date,
mln.attribute7 third_extension_project,
mln.attribute8 third_extension_date,
mln.attribute9 fourth_extension_project,
mln.attribute10 fourth_extension_date, msi.organization_id,
msi.inventory_item_id
FROM apps.mtl_system_items_b msi,
apps.mtl_onhand_quantities moq,
apps.mtl_parameters mp,
apps.hr_organization_units hou,
apps.mtl_item_locations_kfv mil,
apps.mtl_lot_numbers mln,
apps.cst_item_costs cic,
apps.fnd_user fu1,
apps.fnd_user fu2,
apps.mtl_category_sets_tl mcst,
apps.mtl_category_sets_b mcs,
apps.mtl_categories_kfv mc,
apps.mtl_item_categories mic,
apps.mtl_descr_element_values element10,
apps.mtl_descr_element_values element20,
apps.mtl_descr_element_values element30,
apps.mtl_descr_element_values element40,
apps.mtl_descr_element_values element50,
apps.mtl_descr_element_values element60,
apps.mtl_descr_element_values element70,
apps.mtl_cross_references mcr1,
apps.mtl_cross_references mcr2,
apps.mtl_cross_references mcr3,
apps.mtl_cross_references mcr4,
apps.mtl_item_sub_inventories misi,
(SELECT msec.secondary_inventory_name, msec.organization_id
FROM apps.mtl_secondary_inventories msec
WHERE msec.secondary_inventory_name IN ('SUBIN_A','SUBINV_B')) sinv
WHERE 1 = 1
AND hou.organization_id = mp.organization_id
AND moq.locator_id = mil.inventory_location_id(+)
AND moq.subinventory_code = mil.subinventory_code(+)
AND moq.organization_id = mil.organization_id(+)
AND mil.enabled_flag(+) = 'Y'
AND msi.inventory_item_status_code = 'Active'
AND msi.inventory_item_id = moq.inventory_item_id(+)
AND msi.organization_id = moq.organization_id(+)
AND mln.lot_number(+) = moq.lot_number
AND mln.organization_id(+) = moq.organization_id
AND mln.inventory_item_id(+) = moq.inventory_item_id
AND msi.inventory_item_id = cic.inventory_item_id(+)
AND msi.organization_id = cic.organization_id(+)
AND fu2.user_id(+) = moq.last_updated_by
AND fu1.user_id(+) = moq.created_by
AND mc.structure_id = mcs.structure_id
AND mc.category_id = mic.category_id
AND mic.inventory_item_id(+) = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mic.category_set_id = mcs.category_set_id
AND mcs.category_set_id = mcst.category_set_id
AND mcst.category_set_name = 'Inventory'
AND element10.element_name(+) = 'Indications'
AND element10.inventory_item_id(+) = msi.inventory_item_id
AND element20.element_name(+) = 'Dose'
AND element20.inventory_item_id(+) = msi.inventory_item_id
AND element30.element_name(+) = 'Customer'
AND element30.inventory_item_id(+) = msi.inventory_item_id
AND element40.element_name(+) = 'Adult/Ped'
AND element40.inventory_item_id(+) = msi.inventory_item_id
AND element50.element_name(+) = 'Pregnancy Class'
AND element50.inventory_item_id(+) = msi.inventory_item_id
AND element60.element_name(+) = 'Product Identifier'
AND element60.inventory_item_id(+) = msi.inventory_item_id
AND element70.element_name(+) = 'Conv UOM'
AND element70.inventory_item_id(+) = msi.inventory_item_id
AND msi.inventory_item_id = mcr1.inventory_item_id(+)
AND mcr1.cross_reference_type(+) = 'NDC'
AND msi.inventory_item_id = mcr2.inventory_item_id(+)
AND mcr2.cross_reference_type(+) = 'NSN'
AND msi.inventory_item_id = mcr3.inventory_item_id(+)
AND mcr3.cross_reference_type(+) = 'NDA'
AND msi.inventory_item_id = mcr4.inventory_item_id(+)
AND mcr4.cross_reference_type(+) = 'FMCS'
AND moq.organization_id = misi.organization_id(+)
AND moq.inventory_item_id = misi.inventory_item_id(+)
AND moq.subinventory_code = misi.secondary_inventory(+)
AND (moq.subinventory_code IN ('SUBIN_A','SUBINV_B') OR (moq.subinventory_code IS NULL AND sinv.secondary_inventory_name IN ('SUBIN_A','SUBINV_B')))
AND msi.organization_id = mp.organization_id
AND msi.organization_id = sinv.organization_id
GROUP BY hou.NAME,
msi.segment1,
mp.organization_code,
decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name),
mil.concatenated_segments,
mil.segment1,
mil.segment2,
mil.segment3,
mil.segment4,
mil.segment5,
mil.segment6,
mil.segment7,
msi.description,
msi.primary_uom_code,
moq.lot_number,
mln.expiration_date,
cic.item_cost,
msi.list_price_per_unit,
mil.attribute1,
fu1.user_name,
fu2.user_name,
msi.attribute1,
msi.attribute2,
msi.attribute3,
TO_NUMBER (msi.attribute4),
TO_NUMBER (msi.attribute5),
TO_NUMBER (msi.attribute6),
mcst.category_set_name,
mc.concatenated_segments,
mil.attribute2,
mil.attribute3,
mil.attribute4,
element10.element_value,
element20.element_value,
element30.element_value,
element40.element_value,
element50.element_value,
element60.element_value,
element70.element_value,
mln.attribute1,
mc.segment1,
mc.segment2,
mc.segment3,
mc.segment4,
mcr1.cross_reference,
mcr2.cross_reference,
mcr3.cross_reference,
mcr4.cross_reference,
misi.min_minmax_quantity,
misi.max_minmax_quantity,
msi.min_minmax_quantity,
msi.max_minmax_quantity,
mln.attribute2,
mln.attribute3,
mln.attribute4,
mln.attribute5,
mln.attribute6,
mln.attribute7,
mln.attribute8,
mln.attribute9,
mln.attribute10,
msi.organization_id,
msi.inventory_item_id
ORDER BY 4;SUPPLIER QUERY
SUPPLIER QUERYSELECT aps.segment1 SUPPLIER_NUMBER, aps.vendor_name SUPPLIER_NAME, apss.city,apsc.first_name||' '||apsc.last_name AS "ContactName", apsc.PHONE, apsc.FAX_AREA_CODE||'-'||apsc.FAX as "FAX NUMBER", APSC.EMAIL_ADDRESS EMAIL_ID FROM ap_suppliers aps, ap_supplier_sites_all apss, ap_supplier_contacts apsc WHERE 1 = 1 AND aps.vendor_id = apss.vendor_id AND apss.vendor_site_id = apsc.vendor_site_id ********************************************************* select pov.vendor_id, pov.vendor_name supplier, pov.vendor_type_lookup_code, sl.location_code shipto_location, bl.location_code billto_location, pov.customer_num, pov.ship_via_lookup_code, pov.fob_lookup_code, rt.name terms, pov.set_of_books_id, pov.credit_status_lookup_code, pov.credit_limit from ra_terms rt, hr_locations bl, hr_locations sl, po_vendors pov where pov.vendor_name like ‘Abb%’ and pov.ship_to_location_id=sl.location_id(+) and pov.bill_to_location_id=bl.location_id(+) and pov.terms_id=rt.term_id(+) order by 1 / select pov.vendor_name Supplier, povs.vendor_site_id, povs.vendor_site_code Site, povs.address_line1 A1ddress, povs.address_line2 A2ddress, povs.address_line3 A3ddress, povs.city||’, ‘|| povs.state||’ ‘|| povs.zip A4ddress, povs.ship_to_location_id, povs.bill_to_location_id, povs.ship_via_lookup_code, povs.freight_terms_lookup_code, povs.fob_lookup_code from po_vendors pov, po_vendor_sites povs where pov.vendor_id=601 and pov.vendor_id=povs.vendor_id order by 1 / Query to find Supplier contacts : select vc.vendor_contact_id, vc.vendor_site_id, vc.first_name, vc.middle_name, vc.last_name, vc.prefix, vc.title, vc.mail_stop, vc.area_code, vc.phone, vc.department, vc.email_address, vc.url, vc.alt_area_code, vc.alt_phone, vc.fax_area_code, vc.inactive_date, vc.fax from po_vendor_contacts vc where vc.vendor_site_id=4556 order by 1
PO_AP LINK QUERIES (2-WAY, 3-WAY,4-WAY)
AP-PO Link Queries
-- VENDOR, PO AND INVOICE DETAILS (2 WAY MATCH)
SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'N'
-- VENDOR, PO AND INVOICE DETAILS (3 WAY MATCH)
SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'Y'
-- VENDOR, PO AND INVOICE DETAILS (4 WAY MATCH)
SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'Y'
AND g1.receipt_required_flag = 'Y'
-- VENDOR, PO, INVOICE AND PAYMENT DETAILS
SELECT DISTINCT a.org_id "ORG ID", e.segment1 "VENDOR NUMBER",
e.vendor_name "VENDOR NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
d.segment1 "PO NUM", d.type_lookup_code "PO TYPE",
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED",
g.item_description "ITEM DESCRIPTION",
g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag,
'Y', 'Approved'
)
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid, h.check_id, i.check_number,
h.invoice_payment_id,
TO_CHAR (TRUNC (i.check_date)) "PAYMENT DATE"
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
ap.ap_invoice_payments_all h,
ap.ap_checks_all i
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND c.po_line_id = g.po_line_id
AND a.invoice_id = h.invoice_id
AND h.check_id = i.check_id
AND c.po_header_id IS NOT NULL
AND a.payment_status_flag = 'Y'
AND d.type_lookup_code != 'BLANKET'
ORDER BY E.VENDOR_NAMESQL * Loader
SQL * Loader :
==================
It is one of the Oracle Tool will be used to transfer the data from Flat files into
Oracle Base tables.
When we are developing the Interfaces or Conversion program in Oracle Applications
then we will be using the SQL Loader Programs.
while working with SQL * Loader we will come across 5 Files.
1)Flat File OR data File:
Data file contains the data in the specific format which we will receive from the
client. this file will be generated by the legacy system(Other Systems) program.
It may be either .txt format
.dat
.csv(Comma Separated View) Excell sheet.
Most of the times we will receive in the (.csv) format
2)Control File :
-----------------
Is nothing but a SQL*Loader Program will be used to load the data from file into table.
we will develop the control File
Execute the Control File.
While developing the Control file we will specify follwoing things.
1)Data File path
2)Database Table Name
3)Column Mapping.
Control file Extension is (.ctl)
3)Bad File:
-----------
when we execute the COntrol file if any records are rejected by SQL *Loader then we
will find those rejecteds record in the Bad file.
SQl loader recjects the records if there are any datatype mismatch
Internal Errors
Data file Format problem
Bad File Extension is (.bad)
4)Discard File:
---------------
when we execute the COntrol file if any records are rejected by Control File then we
will find those rejecteds record in the Discard file.
Control file Recjects the record when the record is not satisfying the record which
are not satisfied the condition specified in the Control file.
Discard File Extension is (.dis)
5)Log File :
-------------
It contains the Log information like Program start time,End Time
No of succesfully records upload
Rejected Records into the Bad file
Rejected records into the Discard file
Any errors are there in the Control file those error message details we will find.
Log file Extension is (.log)
Control File Syntax:
----------------------
LOAD DATA
INFILE 'Data File Path'
Insert into table <Table Name>
FIELDS TERMINATED by ',' OPTIONALLY ENCLOSED BY'"'
TRAILING NULLCOLS
(Column1 ,
column2 ,
column3 ,......)
Execution of Control File :
-----------------------------
Goto Command Prompt where the SQl loader is Installed.
D:Oracle\Proddb\8.1.7\bin\ SQLLDR Username/Password@PROD
Control = Control file Path.
Request Set , Incompatibility and :$FLEX$ and :$PROFILES
Request Set:
============
Colection of Concurrent Programs which will be used to submit the Cnocurrent Programs
either sequentially or Paraalley multiple programs
It is also like Request group but in Requuest group we can submit only one program at
a time from SRS Window.
where as in Request set we can submit multiple programs at a time.
1)Select the Programs which we would like to group in the set
Ex:
Active Responsibilities
Active Users
Compile Reports
2)Open the Request Set form Select the button called Request set wizard and
enter the concurrent Program list.
Concurrent =>Set
3)Open the Request group Form attach the Request set by selecting the Type = Set
and attach the Request set.
4)Goto the SRS Window select the Option called Request set instead of Single Request.
Incompatibility:
================
Incompatibility is nothing but not compatible with the current concurrent program
For Ex
If we have three program A B C
If A program is running in the server system should not run the B and C programs
that time we wii define the Incompatibility
While createion of the A Program
Select the button called Incompatibility and enter the B and C Programs.
Run Alone Check box :if we would like to make the Program is not compatible with all
other concurrent Program then we will check this check box.
Use in SRS Window: Be default this check box will be enabled we uncheck this we can
find the Program at SRS window we have to submit from backend by using fnd_request
API.
Copy To button: While customizing the Concurrent Programs(Reports,Package.....)
we are suppose to create new concurrent Program with diff name then we will
go for using the Copy To button.
note: By default every concurrent Program will be executed in the CBO(Cost Based
Optimizer) if we would like to execute in the RBO(Rule Bases Optimizer)mode then
we will use the Session control buttion we will set the Rule Option.
:$FLEX$
:$PROFILES$
These two are Oracle apps reserved words will be used in the Value set creation
:$FLEX$: This will be used to Retrieve the Previous parameter value whatever we have
selected.
Syntax : :$FLEX$.Previous Parameter VAlue set Name.
For Ex: We have two Parameters
Supplier Name : Table Value set
Supplier Site Code : Table VAlue set
based on the Suplier name we are suppose to get the Site codes in the Second parameter
SELECT VENDOR_NAME FROM PO_VENDORS - First VAlue set
SELECT VENDOR_SITE_CODE FROM PO_vendor_sites_All -Second Value Set
where vendor_name = Whatever user has selected in the First PArameter
(To get this value we will use :$FLEX$.Previous Value set name).
23SUPPLIER - First Value set Name
23SITE
WHERE VENDOR_ID IN(SELECT VENDOR_ID
FROM PO_VENDORS
WHERE VENDOR_NAME = :$FLEX$.23SUPPLIER)
:$PROFILES$: This will be used to Get the Profile value in the Table Value set or
from the front end.
To get Profile values from backend we are using Fnd_Profile.Value or Fnd_Profile.get()
Syntax : :$PROFILES$.Profile Name
SELECT SEGMENT1
FROM PO_HEADERS_ALL
22USER : 204
23USER : 887
24USER : 911
SELECT SEGMENT1
FROM PO_HEADER_ALL
WHERE ORG_ID = :$PROFILES$.ORG_ID
Ex: Display the PO's which are created by the current User
If 22user ope the LOV it has to display the PO's which are created by 22 user
SELECT SEGMENT1
FROM PO_HEADERS_ALL
WHERE CREATED_BY = :$PROFILES$.USER_ID
Report Registration Steps:
Report Registration Steps:
-------------------
1)Develop the report (.rdf)as per client Requirement by using reports 6i Builder
2)Move the report from Local mechine into Server
CUS_TOP\11.5.0\Reports\US\ .rdf
PO_TOP\11.5.0\Reports\US\ .rdf
3)Select System Administrator and create EXECUTABLE
1)Executable Name
2)ApplicationName
3)Execution Method
4)Report(.rdf) file Name
4)Create Concurrent Program and attach 1)EXECUTABLE
2)PARAMETERS
3)INCOMPATIBILITIES
5)Create Request Group and attach Concurrent Program
6)Create Responsibility 1)Request Group
2)Data Group
3)Menu
7)Create user, attach Responsibility to the user.
8)User will select the Responsibility and goto SRS(Standard Request Submission) Window
submit the Request.
Navigations:
============
Executable = Concurrent=>Program=>Executable
Concurrent Program = Concurrent=>Program=>Define
Request Group = Security=>Responsibility=>Request
Responsibility = Security=>responsibility=>Define
User = Security=>User=>Define
SRS Window = View Menu=>Request =>Submit New request=>Ok=>Select Program
=>Submit=>No=>Find=>View Output
Profiles
Profile :
Profile is one of the changable option it will change the way of
application execution.
When User Log in to the application and select the the resp or Appl
system will automatically captures all the profile value as per the
profile values application will run.
Ex: If client have three Organizations 1)Hyd
2)Ban
3)Chn
If "hyd" users connect to the Application system will retrive the
data from database which is related to the Hyderabad branch.
If user is working for 'CHN' brnach then chennai branch setups or data
will be retrieved.
For every user we will assign the Profile value
Ex: Operation
Position - Profile Name
Profile Values
--------------
Manager
Supervisior
Clerk
Operator
Trainess
When we want assign any profile value we have four levels
we have to select any one of the level.
Profile Level Profile Profile Value
------------- ------- --------------
User - OPERATIONS - Print - 10(This is for for Operations)
Responsibility - 22Responsi - Print - 5(This is for 22resp users)
Application - GL Applica - Print - 4(This is for GL App Users)
Site - --- - Print - 2(This is for ALL Users)
Site : this is lowest level to assign the Profile values site values
are applicable for all the users.when we install Application by default
site level values will be assigned.
Application: These values are applicable for the users who are having
the access for the application. If user is eligible for both
application and site level values then application level value will
override the site level value.
Responsibility:We will select the responsibility name assign the value
which is applicalbe only for the users who are having the access for
specified responsibility.
Responsibility level value will override both application and site
level values.
User: This is highest level in the profile option.
we will select the user name and assign the profile value which is
applicable only for this user.
User level value will override all other profile level values.
Diff between Application and Responsibility:
============================================
Both are Group of Forms(Menu)
Group of ConcurrentPrograms(Request Group)
Group of Users (Data group)
But Application as per the Business functionality requirement
Responsibility will group as per the position requirement.
Some of the Imp Profile Names:
GL:Set Of Books
MO:Operating Unit
Hr:Business Groups
MFG_ORGANIZATION_ID
USER_ID
RESP_ID
USERNAME
RESP_NAME and so on..........
note: In the Projects most of the profile values will be assigned at the Responsibility
level.
Diff Between Application and Responsibility:
=========== ===============
Applciation is nothing Colletion of Forms,Reports and Program which are related for
specific business functionality.
Responsibility is nothing but Colletion of Forms,Reports and Program which are related for
specific Position in the Organization.
For Ex : We have to create One Responsibility For the Clerk. Which is accesable by all
the Clerks.
It Contains the Forms and Reports which are required for the Clerk.
We have to Create new Responsibility for the Manager,Which is accesable by all the
Managers.
It COntains the Forms and Reports which are required for the manager.
Where as Application includes all the Forms,Reports and Programs.If we assign the
application to the user he will access all the forms and Reports.
Intead of that we will create the responsibility and we will assign to the User.
Common Profiles:
================
Gl:Set of Books: Which is Financial Profile option will be uset to assin SetofBooks
HR:Business Group : Which will used to assign the Business Group
MO:Operating Unit : To assign the Operating Unit (Branch) to the users.
MFG_ORGANIZATION_ID: Will Be used to assign the Manufacturing Organization ID.
USER_ID
USERNAME
RESP_NAME and so on.........
We can find all the Profile details in Application Developer Responsibility.
We can assign the Profile values in System Administrator Responsibility.
Application Developer=>Profile =>Press CTRL+F11 we can find all the profiles.
System administrator=>profile=>System=> Select Profilename,Level =>Find button
then assign the Profile value.
Set Of Books :SOB is nothing but collection of Currency
Calendar
Chart of Accounts.
We will assign the SOB as a profile value to the user as per the Profile value system
will automatically change the application running.
Base on the SOB name we can find the change in the currency and calendar and accounts
SELECT NAME,
CURRENCY_CODE,
PERIOD_SET_NAME,
CHART_OF_ACCOUNTS_ID
FROM GL_SETS_OF_BOOKS
SELECT * FROM GLFV_CHARTS_OF_ACCOUNTS WHERE CHART_OF_ACCOUNTS_ID = 50713
22USER GL:Set Of Books Vision Operations (USA) USD
23USER GL:Set Of Books Vision Korea KRW
24USER GL:Set Of Books Vision Italy ITL
Create Three users
Assign Profile values from System administrator (Profile=>System)
open the GL Form and verify the curency values (GL=>Journal=>Enter=>new Journal)
Note: Most of the profile values will be assigned at Responsibility Level.
Retrieve the Profile Value from Backend:(SQL,PL/SQL,Forms6i,Reports6i)
======================================
Fnd_Profile.Get('ProfileName',
local Variable);
local Variable:= Fnd_Profile.Value('Profile Name');
Both API's will be used to retrieve the Profile value frombackend
Get() is Procedure
Value() is Function
Oracle Has provided both Procedure and Function becuase in some of the areas we can not
use procedure then we can use function.
For Ex: in SELECT clause we can not use procedure we have to go for using the Function.
1)We would like to display the Set of Books name
User name
Respname in the first page of the report.
22USER
23USER
24USER
Ans)
1)Define the Local Variable
2)Goto before Report Trigger write the follwoing API
:P_SOBNAME:= Fnd_Profile.value('GL_SET_OF_BKS_NAME');
:USERNAME := Fnd_Profile.value('USERNAME');
Fnd_Profile.Get('RESP_NAME',
:RESPNAME);
3)Goto Layout model Header section and Display the Variable Name.
4)Submit from Diff Users and test the Output we can find the Difference.
2)Develop the PL/SQL Program for vendor Name updation. Vendor name should be updated
if "OPERATIONS" user submit the Program for other users should not get update.
Parameters are VendorID
VendorName
Create Or Replace Procedure ven_update(Errbuf OUT varchar2,
Retcode OUT varchar2,
v_id IN number,
v_name IN varchar2) as
l_name varchar2(100);
begin
l_name:=Fnd_Profile.value('USERNAME');
If l_name = 'OPERATIONS' then
UPDATE PO_VENDORS
SET VENDOR_NAME = v_name
WHERE VENDOR_ID =v_id;
commit;
Fnd_File.Put_line(Fnd_File.Output,'vendorname has updated succesfully');
Else
Fnd_File.Put_line(Fnd_File.Output,'Access Denied for updateion');
End If;
End;
Note: We can pass the profile value as default value by using Profile default type.
Select Default type = profile
Default Value= Profile Name
When we are passing Profile value as default we are suppose to hide the Parameter
because profile is confidential Information we are not suppose to give permission for
modifications.
Procedure Registration Steps In APPS
Procedure Registration Steps:
1)Develop the procedure and compile at SQL prompt
2)Connect application select system administrator Responsbility create
Executable by selecting the Execution Method as 'PL/SQL Stored Procedure'
3)Create Concurrent Program and attach Executable to the Program and add the
Parameters and incompatible programs.
4)Create Request group attach concurrent Progrtam
5)Attach Request group to the reponsibility
6)Responsiboility will be added to the user.
User will submit the request from SRS Window.
Syntax:
Create Or Replace Procedure <ProcedureName> (Errbuf OUT varchar2,
Retcode OUT varchar2,
P1 IN NUMBER,
P2 IN VARCHAR2,
P3 IN DATE) AS
Local variable,Cursor,Collections Declare;
Begin
If statement
For Loop
Procedure Calling
DBMS_OUTPUT.Put_Line - This will not be used instead of this Fnd_File API will be used
FND_FILE.PUT_LINE(Fnd_File.Log ,'Message'||Variable Name);
FND_FILE.PUT_LINE(Fnd_File.Output,'Message'||Variable Name);
Exception
When Other then
-Exception Statements;
End <Procedure Name>;
PL/SQL Procedure with Parameter:
================================
If we have any user defined Parameters then we have to register these parameters at the
time of Creating the COncurrent Program by selecting the Parameter button
enter the Seqno
Parameter Name
Value Set
Note : Token Field will be disabled.
Here First Parameter value will be passed to the first variable
Second Parameter will be passed to the second variable and so on........
When we are registering the report as C.P then only we required TOKEN field.
Because report builder bind variables may or may not be in the sequence that's why
we have to map with Token field.
Where as in Procedure variables position is fixed then TOKEN field will be disabled.
PURCHASE ORDER USE FULL INFORMATION
PURCHASE ORDER USE FULL INFORMATION
PO Application will be used to capture the purchasing information.
Oracle is developed pre defined forms and as well as Concurrent Programs and other
related programs.Client Directley can use those forms and Programs or client
can customize the existing objects(Forms,Reports,Programs)
During the PO application flow we can find the three types of people
1)Requestor : Employee who require the materials
2)Preparer : Employee who is going to prepare the Document
3)Buyer : Employee who is having the authority to purchase the materials from the
Suppliers.
Requisition: is one of the purchasing document will be prepared by the employee when
ever he required the materials or Services or Training and so on.
we have two types of Requisitions 1)Internal
2)Purchase
Internal requisition will be created if materials are receiving from another Inventory
inside of the organization.
Purchase requisition will be created while purchasing the materials from the Suppliers.
Requisitions=>Requisitions
We will enter the Requisition at three level 1)Header
2)Line
3)Distributions.
Open the Requisition form enter the Reqno and select the type at Header level
Enter the Items information at line level like Item name,qty,unitprice,tax and so on
select Distributions button enter the Distributions details.
Save
Select the Button called Approve button to go for approving the Requisition Document
Open the Requisition summary form.
Enter the Reqno select find button we can find the Requisition status wether it is
approved or not.
select Tools menu => View Action History to find the history details
Select Tools Menu =>Control option to Cancel the requisition.
RFQ Document:(Request For Quotation)
=============
Once the Requisition is Approved Buyer will prepare thre RFQ document which will be
delivered to the supplier. Supplier will respond for that with quotation.
we have Three types of RFQ documents
BID RFQ:This will be prepared for the secific fixed quantity and there won't be any
PriceBraeaks(Discounts).
catalog RFQ: This will be create for te materials which we will purchase from the
suppliers regularley , and large number of quantity. Here we can specify the
Price Breaks.
Standard RFQ: This will be prepared for the Items which we will purchase only once
not very often,Here we can include the Discounts information at different
auantity levels.
RFQ Information will be entered at 3 Level
1)Headers
2)Lines
3)Price Breaks(CATALOG,STANDARD) or Shippments (Only for Bid RFQ)
Terms And Conditions:
While creation of the RFQ documents we will select the Terms button and we will enter
the terms abd condition details.
Payment Terms: When Organization is going to make the payment and Interest rates
Fright Terms: Who is going to Bear the Tansportation chargers wether Buyer or Supplier
FOB(FreeOnBoard): If any materials damage or any missing quantity is there then the
the responsiboility of those materials.
Carrier : In which Transportation Company Organization Required Materials
Transportation company Name.
Open the RFQ Form
RFQ and Quotations=>RFQ's
select TYpe and Dates and so on
enter the Items details at line level
select terms button enter the Terms and Condition Details
Select the Price Braks button enter the Price break details
Save
Select the suppliers button enter the suplier details (Who are receiving this Document)
Select the Button called Add from List to Include the supplier list automatically.
Buyer Name : TABLE (Internally buyer ID should pass) - Optional
RFQNo Shipto
Type Billto
Due date Curr
Close date Total
creation date User(created_by)
Lineno Item UOM Price Shipno Qty Price Discount
-----
Quotations:
===========
Quotation is another purchasing document we will receive from the Supplier which
contains the supplier quote details , Price, Payment terms and so on.
Whatever the quotations we have received from the supplier we will enter in the system
through form.
We have three types of Quotations 1)Bid 2)Catalog 3)Standard
For Bid RFQ we will receive Bid quotation from the Supplier
For Catalog RFQ we will receive Catalog quotation from the Supplier
For Standard RFQ we will receive Standard quotation from the Supplier.
After enter all the quotations in the system management will do quote analysis as per
that one best quotation will be elected as Purchase Order.
Quotation Report
Item Name (Table Value set MTL_SYSTEM_ITEMS_B Segment1)
QuoteNo Type Cdate Supplier Site ContactPerson Buyer Created(UserName)
po_headers_all
po_lines_all
mtl_system_items_b
po_vendors
po_vendor_sites_all
po_vendor_contacts
per_all_people_f
fnd_user
Auto Create:
============
It is one of the Purchasing feature to create the RFQ and PO documents automatically
by using requisition lines.
1)Create Requisition and approve
2)Open the AutoCreate form
3)Select Clear button enter the RequisitionNO
4)Select find button which will shows all the requisition lines
select the lines whatever we want to include into the RFQ
5)select Action = Create to create new RFQ
AddTo to add lines to exisiting to RFQ
6)Select DocumentType = RFQ
7)select Automatic button which will create RFQ document automatically .
Purchase Order :
================
PO is one of the Main document which will be prepared and approved by the buyer and
send it to the supplier. which contains the following information
terms and Conditions
Items deails
Qty,Price
Distiribution and Shipment Details and so on.
We have four types of Purchase Order 1)STANDARD
2)PLANNED
3)BLANKET
4)CONTRACT
Purchase Orders=> Purchase Orders
Open the PO form enter the Inforamtion at header level select line level inforamtion
enter the items and quantity,price details
select shippments button enter the shippment details select the Distributions button
enter the Distribution Detauils.
Save
Select the Button called Approve (Uncheck Email Check Box) , Document will be submitted
for approval.
open the Purchase Order summary form enter PO number Select Find button we can find
the status of the Purchase order.
Goto Tools menu
Action History => We can find who hs submitted for Approve /Reject /Cancel details
Copy Document => To Create Another PO based on this PO
Control => To Close the Purchase Order or to cancel the Purchase Order.
Purchase Order Report
=====================
POno : Buyer:
Potype : Supplier:
ShipTo : Supplier Site:
BillTo : Contact :
Cdate : Status : POTotal :
payment Terms: Fright Charges: FOB: Carrier:
Lineno Item Desc Qty price Shipno ShiptoLoc ShipToOrg Qty Distno Distqty Requestor
----- ---- ---- --- ----- ------ -------- --------- --- ----- ------ ------
KFF AND KFF vs. DFF: Comparison
KFF (Key fLex Fields)
=====================
KFF will be used to Capture the Key Information in code language for every code
there will be specific meaning in the application.
For Ex: we can find some of the fileds like follows
Code Combination 01-001-1540-1234-0000
01-Operations
001-HRMS
1540-Furniture
1234-Sales Account
0000-No Product
Description : Operations-HRMS-Furniture-Sales Account-No Prodcut
like this we can find the meaning in the application for every code.
We will use the Segment Columns to Capture the KFF Data.
We can find the Segment coumns in most of the Tables like
SEGMENT1,SEGMENT2,SEGMENT3,..........SEGMENT30.
Diff Between DFF and KFF
DFF KFF
----- ---
1)DFF Will be used to capture the Extra 1)KFF will be used to Capture the Key
Information. Information
2)ATTRIBUTE Columns will be used 2)SEGMENT columns will be used
3)We can have max DFF in the Application 3)We have 30KFF already defined by Oracle
there is no Limit we can customize existing KFF. We are not
suppose to define the new KFF. We will not
get support from Oracle.
4)In DFF we will Context Field to define 4)In KFF we will define Structure Column
Multiple Strutures to define the Multiple Structures.P_FLEXDATA: Is a Lexical parameter will be used to store the default value as concatenation of all the segment columns. This is required because if client changes the segment structure from front end again we have to change all the structure. instead of that we can concatenate all the segments. lexical parameter means datatype :char width : min 600 p_struct_num: It will be used to store the kff structure number which is required if KFF is having the multiple structures. Ex: 101 default value FNDFLEXSQL: ---------- This is One of the User exit will be used to retrive the segment values from database and will be used in the before report trigger Parameters: CODE -KFF Code num -KFF structure Number appl_short_name -KFF Application ShortName OutpuT -Lexical parameter Name Display -Display all the segments Mode -'SELECT' FNDFLEXIDVAL: ----------- This user exit we will define inside of the formuls column to display the data in the layout. define the formula column call the user exit with follwoing parameters Parameters: CODE -KFF Code num -KFF structure Number appl_short_name -KFF Application ShortName OutpuT -Lexical parameter Name Display -Display all the segments Mode -'SELECT' data -Lexical Parameter Name Value -Formula Column Name Note : To Display the KFF description we will define one more formula column and we will call again 'FND FLEXIDVAL' user exit with parameter called 'Description' instead of 'Value' Parameter.
Interfaces
Interfaces:
-------------
Interface is one of the Program which will be used to transfer the data from Oracle
database tables in to flat file (or)
Flat files into Database tables
We have two types of Interfaces.
1)Inbound Interface
2)outbound Interface
Outbound Interface will be used to extract the data from oracle Database tables into
the flat files.
Inbound Interface will be used to upload the data from legacy system (Flat files) into
Oracle Applications base tables.
While developing the outbound Interface we will use UTL_File to Extract the data.
While Developing the Inbound interface we will use SQL * loader to import the data
into base taqbles.
Converstion Vs Interfaces Interfaces: It is schedule concurrent Process which will be executed multiple times We will not be knowing flat file Volume We need to handle all the expected exceptions Error Reports and sending mail alerts, If any error occurs Only in Enhancement or customization projects Conversion: One time data transfer We will be know exact falt file Volume No need to know the exceptions Not required to upload all records Only in Implementation,Migration or up-gradation projects
UTL_FILE Package : ================== this is One of the PL/SQL Package which will be used to transfer the data from table to files from files to tables But when we are working for file to table we will use SQl *Loader to transfer from table to file we have no alternative we have to use UTL_FILE. We will use following three functions to generate the file. 1)Utl_File.fopen = To open (or) Create the file 2)Utl_File.Put_line = To Transfer the data into the File. 3)Utl_File.fclose = To close the File after Data transfer. outbound Interface Process: ============================= 1)Develop the PL/SQL Program (Either Procedure or Package) 2)Write the Cursor to retrieve the data from database tables. 3)Create file or Open the File by using UTL_File.fopen(). 4)Open the Cursor 5)If any validations are there write the validations 6)Transfer the Data into File by using UTL_File.Put_Line(). 7)Close the Cursor. 8)Close the File by using UTL_File.fclose(); 9)Register the Procedure or Package as Concurrent Program and submit from SRS Window. For Ex : We required the flat file with following Format. Supplierno,Supliername,Cdate,Sitename Develop the Program to Transfer Supplier Purchase orders datainto file Supplier No from Supplier No To PoNumber,POtype,Amount,Cdate,No,Name,Site Validations: 1)If Parameters are null select all the supplier details 2)If 'from' is given 'To' is not given take till Last Supplier 3)If 'To' is given and 'from' is not given take from first. 4)If Purchase order amount is more then 3500 then only transfer into file.
FLEXFIELDS
FLEXFIELDS:
===========
Flexfield are made up with Attribute columns or Segment columns .
which are more flexible than the normal fields.
we have two type of flexfields
1)DFF (Descriptive Flex Field)
2)KFF (Key Flex Field)
DFF: It will be used to capture the Extra information from the end user
without change the code in the form and without Alter the DB object.
ATTRIBUTE Columns will be used to Capture the DFF data.
KFF: it will be used to Capture the Key information from the User
in code language for every code there will be a specific meaning.
SEGMENT Columns will be used Capture the KFF Data
We can find all the flexfield details in Application Developer
Responsibility
Flexfield=>Descriptive=>register=> CTRL+F11 for all DFF
Flexfield=>Key =>register=> CTRL+F11 for all KFF
We will Use the Segment form to Customize the DFF.DFF CUSTOMIZATION STEPS
DFF CUSTOMIZATION:1)Open the form where DFF is Implemented. and Query the Data
2)find the DFF name from Help Menu =>Diagnastcs =>Examine
Select $DESCRIPTIVE_FLEXFIELD$ in the Block field
select DFF name in the "Field"
we will get the DFF Title in the Value field.
Copy the DFF title
3)goto Application Developer Responsibility.
flexfield=>Descriptive=>register
4)Query the records by using the DFF Title.
Select columns button and find wether Attribute
columns are enabled or disabled.
5)Goto Segments form and Query the records by using Title.
6)Uncheck the Check box called "freeze Flexfield Defination"
7)Select Segents button and enter the new fields and save transactions.
8)Check the check box called "freeze Flexfield Defination"
and click the Compile Button.
9)System will change the DFF structure and creates
a descriptive flexfield View.
Diff Between InboundInterface and Conversion:
Diff Between InboundInterface and Conversion:
===================== ================
Interface Conversion
========= ==========
1)Interface will be used to upload the 1)Conversion is one-Time Data Transfer
data multiple times.
Regularley every week or month or day
2)Client Will be using Legacy system. 2)Client will use Only ORacle Applications.
and also Oracle Applications once data is uploaed from legacy then legacy
system will be closed.
3)We will not be knowing the Data file 3)here we will be knowing the data file volume
volume. exactley.
4)In Customization and as well as 4)Normally in the Implementation Porjects we
implementation projectes we will be will be using.
developing Interface.
PURCHASE ORDER INTERFACE(CODE)
PURCHASE ORDER INTERFACE
CREATE OR REPLACE PROCEDURE PO_Int(Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2) AS
CURSOR c1 IS SELECT * FROM xx_staging_headers;
CURSOR c2 IS SELECT * FROM xx_staging_lines1;
l_vendor_id number(10);
l_item varchar2(150);
l_flag varchar2(4) default 'A';
l_msg varchar2(200);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
COMMIT;
FOR x1 IN c1 LOOP
BEGIN
SELECT vendor_id
INTO l_vendor_id
FROM po_vendors
WHERE vendor_name = x1.VENDOR_NAME;
-- AND ORG_ID = Fnd_Profile.Value('ORG_ID');
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_vendor_id := 0;
l_msg := 'Vendor id is Not in SYSTEM';
END;
IF l_flag != 'E' THEN
INSERT INTO po_headers_interface
(
INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,creation_date
,APPROVAL_STATUS
,APPROVED_DATE
,FREIGHT_CARRIER
,FOB
,FREIGHT_TERMS
)
VALUES
(
x1.INTERFACE_HEADER_ID
,x1.batch_id
,x1.action
,x1.org_id --"Puffer-Sweiven"
,x1.document_type_code --done
,x1.CURRENCY_CODE --done
,x1.AGENT_NAME --done
,x1.VENDOR_NAME
,x1.VENDOR_SITE_CODE
,x1.SHIP_TO_LOCATION
,x1.BILL_TO_LOCATION
,SYSDATE-10 --done
,x1.APPROVAL_STATUS
,SYSDATE --done
,x1.FREIGHT_CARRIER
,x1.FOB
,x1.FREIGHT_TERMS
);
end if;
END LOOP;
FOR x2 IN c2
LOOP
l_flag := 'A';
--Item Validation
begin
select segment1
into l_item
from mtl_system_items_b
where segment1 = x2.item
AND ORGANIZATION_ID = fnd_profile.value('ORG_ID');
exception
when others then
l_flag := 'E';
l_vendor_id := 0;
l_msg := 'Item is not valid Item';
END;
--End of the Item Validation
if l_flag != 'E' then
INSERT INTO PO_LINES_INTERFACE
(
INTERFACE_LINE_ID
,INTERFACE_HEADER_ID
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,item_id
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,NEED_BY_DATE
,PROMISED_DATE
,list_price_per_unit
)
VALUES
(
x2.INTERFACE_LINE_ID
,x2.INTERFACE_HEADER_ID
,x2.LINE_NUM
,x2.SHIPMENT_NUM
,x2.LINE_TYPE
,x2.item
,x2.item_description
,x2.item_id
,x2.uom_code
,x2.quantity
,x2.unit_price
,x2.SHIP_TO_ORGANIZATION_CODE
,x2.ship_to_location
,SYSDATE --done
,SYSDATE --done
,x2.list_price_per_unit
);
end if;
END LOOP;
COMMIT;
END PO_Int;
/PURCHASE ORDER QUERY
PURCHASE ORDER QUERY
SELECT PHA.PO_HEADER_ID ,
pha.SEGMENT1,
pha.revision_num,
pha.type_lookup_code,
trunc(pha.creation_date) Cdate,
PV.VENDOR_NAME ,
PVS.VENDOR_SITE_CODE ,
(PVC.last_NAME||','||PVC.firsT_NAME) ContactName,
H1.LOCATION_CODE ShipTO,
H2.LOCATION_CODE BillTo,
pha.currency_code CUrr,
PPF.FULL_NAME Buyer,
pha.AUTHORIZATION_STATUS Status,
pha.COMMENTS PODesc,
SUM(pla.quantity*pla.unit_price) PoTotal
FROM PO_HEADERS_ALL PHA,
PO_VENDORS PV ,
PO_VENDOR_SITES_ALL PVS,
PO_VENDOR_CONTACTS PVC,
HR_LOCATIONS H1 ,
HR_LOCATIONS H2 ,
PER_ALL_PEOPLE_F ppf,
PO_LINES_ALL pla
WHERE pha.SEGMENT1 = '3478'
AND pha.TYPE_LOOKUP_CODE = 'STANDARD'
AND PV.VENDOR_ID = PHA.VENDOR_ID
AND PVS.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
AND PVC.VENDOR_CONTACT_ID= PHA.VENDOR_CONTACT_ID
AND H1.LOCATION_ID = PHA.SHIP_TO_LOCATION_ID
AND H2.LOCATION_ID = PHA.BILL_TO_LOCATION_ID
AND ppf.person_id = PHA.AGENT_ID
AND pha.po_header_id = PLA.po_header_id
GROUP BY
pha.SEGMENT1,
pha.revision_num,
pha.type_lookup_code,
trunc(pha.creation_date),
PV.VENDOR_NAME ,
PVS.VENDOR_SITE_CODE ,
(PVC.last_NAME||','||PVC.firsT_NAME) ,
H1.LOCATION_CODE ,
H2.LOCATION_CODE ,
pha.currency_code ,
PPF.FULL_NAME ,
pha.AUTHORIZATION_STATUS ,
pha.COMMENTS ,
PHA.PO_HEADER_ID - 12160
Line Level
===========
SELECT PLA.LINE_NUM,
PLT.LINE_TYPE,
MSI.SEGMENT1 Item,
(mc.segment1||','||mc.segment2) Category,
msi.description Itemdesc,
MSI.PRIMARY_UOM_CODE UOM,
pla.quantity,
pla.unit_price
FROM PO_LINES_ALL PLA,
PO_LINE_TYPES PLT,
MTL_SYSTEM_ITEMS_B MSI,
MTL_CATEGORIES MC
WHERE PLA.PO_HEADER_ID = 12160
AND PLA.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND MSI.ORGANIZATION_ID = PLA.ORG_ID
AND MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID
and mc.CATEGORY_ID = pla.CATEGORY_ID
POSITION DEFINE IN HRMS
POSITION DEFINE :
Job Positions
==== ==========
Director = Managing Director
Manager = Finance Manager
Production Manager
Clerk = Senior Clerk
Assisten Clerk
operators = Mechine Operator
System operator
1)Open the Position Form
Work structure=>position=>Description
2)Select New Button =>Set Effective date
3)Enter the Position Name as per the Position KFF structure
4)Select Position type (Ex : Shared)
Single Incumbent :
meaning that only one employee is allowed to hold the position at any time
Shared :
meaning there can be several incumbents, up to the value of the FTE field
Pooled (Public Sector only) :
meaning the position is loosely defined so rules about FTE and hours are not enforced
by the system
None : which you can select if you do not need to record position types
5)Select the Organization Name where we can use the Position
6)Select the Job Under which Job we are defining this Position
7)Select Hiring Status = 'Active'
Active : We can Recruit the Employees
Praposed : We can not recruit Employees Position is Under Process.
8)Goto Hirng Information Tab enter the FTE and Heade Count
FTE =FUll Time Equivalent
Head Count =Number of Employes we can recruit under this Position.
Define Position hierarchy:
==========================
Workstructures=>Position=>Hierarchy
1)Enter the Position hierarchy Name and check the Primary check Box
Save
2)Enter the Version number and Date From
save
3)Place the Cursor in the Position Name Field Press CTRL+F11
4)Go for Defining the Subordinates as per the requirement by using Down and Up Arrow marks
Save.
5)open the Diagrammer form we can find the Position Hierarchy Diagramatically chart wise
and we can also find the Position Holder Details.
MD
FM PM
SC MO
JC SO
Mass Move:
==========
If we are going to Transfer the Position from One organization to Another Organization then
we will go for using the Massmove to transfer the Positions
Enter the Name
and Select Source and Target Organization
select Positions Button and select Positions which we would like to transfer
select the button called Execute.
PURCHASING END TO END SETUP STEPS:
PURCHASING END TO END SETUP STEPS:Attach the Profile HR:user Type = HR with Payroll User
1)Locations in HRMS
(US HRMS Manager =.Work Structure=>Location)
2)Business groups in HRMS
Assign the Profiles
SOB Creation
Create Inventory Work Calendar attach to Inventory organization
Inventory=>Setup=>Organizations=>Calendar=>Create Calendar=>Tool Menu=>build
3)Create all Organization(SOB,LE,OU,IO)
4)Organization hierarchy
5)jobs
6)Positions
7)Position hierarchy
8)Create Two Employee 1)Clerk 2)Manager Assign the manager to the Clerk.
9)Submit Program from SRS Window Fill Employee Hierarchy.
9)Creation of Combo Responsibility(PO,INV,GL,AP) attach Menus, attach RequestGroup
10)Attach the Profiles
Businesgroup
Set of Books
Operating Units
Attach the Profile called (MO:Operating Unit for the Responsibility)
11)Run the Porgram called Replicate Seed Data.
12)Goto Purchasing Assign the Set of Books.
PO=>Setup=>Organization=>Set of Books=>Choose
13)Purchasing Options
14)Recceving Options
15)Financial options
16)Find the Documents and assign the Approval hierarchy (IBM hierarchy)
17)Assign the Approve Hierarchy to the All Documents.
Purchasing =>Setup=>Purchasing=>Document Types.
18)Define the Approval Group (Setup=>Approval=>approval Groups)
Assign the Hierarchy to the Document Types.
Purchasing=>Document Types.
19)Assign Approval groups to the Documents.
20)Item Creation in Inventory (Inv=>item=>master Items)
21)Creation SubInventory (Inv=>Setup=>Organizations=>Subinventories)
22)Create Users from sytem administrator to the Employees.
Include the Employee names in the Buyers List.
23)Login with Clerk and create Requisition if limit crossed then goto manager login
open the notification summary and Approve.
Employee Creation
Employee Creation :
====================
1)After applicant is accepted the offer we will go for creation of the employeement
then we will select the Peoiple window
People => Enter and Maintain
Select type = Employee ( we can find three types 1)Applicant (peron who has applied for job)
2)Contingent Worker = For Placement type
3)Employee =>Employement
Select new button enter the employee name and other details
select the Action = Employement and enter
NI Number National Insurenece Number for UK Localization
SocialSecurityNumber for US localization.
save
select assignment button enter the Organization
Job
Position
and Grade.
We are suppose to attach the Supervisior and as well as Payroll and salary details.
If we have defined the payroll for employee then we will attach.
We can also capture employee other information by using Others button
For Example :
Previou Employement
Workpreference
Qualifications
End Employement.
EMPLOYEE CREATION1)People=>Enter and Maintain
Select New Button
2)Enter the Emp First name Last name and select Action = Create Employement
and Enter the NI (National Identifier) number
WE123456A(2Characters 6Digits 1Character).
3)Enter Date of Birth.
Save
4)Select Assignment button enter the Organization.
Job.
Position.
Grade.
5)We will also attach the Payroll and Salary information here itself.
6)and we will enter Other Information by select Other button.
Terminate Employee:
====================
To Terminate the Employee we have to go to Others Button => Terminate Employeement
Specify the reason for Termination and Other.
Salary Detail:
================
Employee Salrary Details will be entered in the Salry and Entries buton after assign the
Payroll to the Employee.
Assignment=>Salary. Combo Responsibility :
Combo Responsibility :
HRMS PO AP GL Combo Responsibility : ====================== Menu ==== Purchasing SuperUser GUI AP_NAVIGATE_GUI12 INV_NAVIGATE GL_SUPERUSER US HRMS Navigator Create New Menu : (ERPMENU) ================== System ADministrator=>Application=>Menu=> attach all these menus in the field called SubMenu : Request Group Creation: ======================= System ADministrator =>Security=>Responsibility=>Request Enter Any Name Application : Manufacturing Type Applicatoion generalledger application purchasing application payables application human resource application inventory Create Responsibility: ======================= Security=>Responsibility=>Define Enter Name Application name : Manufacturing Datagroup : Standard Application name : Manufacturing Request group : Whatever we have created Menu : Whatever we have created save Attach the Resp to user.