ORACLE ERP & APPS TECH

Process steps to Disable ATO items (uncheck ATO flag) Pre-Requisites : 1-> Cancel Schedule program -  AutoCreate Final Assembly Orders 2-> Close or Cancel Internal Orders 3-> Ship & complete Order to close for Awaiting Shipping & Awaiting Returns status order lines. 4-> Accept and close Pre_Billing Acceptance order lines. Action Steps : 1 a-> Cancel all Reservations against the item to un-check ATO flag. 1 b-> May require cancellation of WO (re-check with your functional folks) 2-> Cancel all Sales Order lines against the Items. 3-> Un-Check ATO Flag (REPLENISH_TO_ORDER_FLAG = 'N') 4-> Re-create cancelled Order lines if required.

Script to upload items for a given quote and vendor: --SET SERVEROUTPUT ON; DECLARE V_ITEM_ID           mtl_system_items_b.inventory_item_id%TYPE; V_PRICE             PO_LINES_ALL.UNIT_PRICE%TYPE; V_ITEM_DESC         mtl_system_items_b.DESCRIPTION%TYPE; V_line_num          PO_LINES_ALL.LINE_NUM%TYPE := 0; V_organization_id   mtl_system_items_b.organization_id%TYPE; CURSOR C1 IS select msib.inventory_item_id,msib.segment1 ITEM_NUM,        msib.DESCRIPTION,        msib.PRIMARY_UNIT_OF_MEASURE UOM       ,msib.organization_id,mic.category_id,        (select ITEM_COST    from cst_item_costs   where INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID     and ORGANIZATION_ID = msib.ORGANIZATION_ID     and COST_TYPE_ID =1) price  from mtl_system_items_b msib,       mtl_item_categories mic where msib.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID and msib.ORGANIZATION_ID = mic.ORGANIZATION_ID and msib.organization_id = 12 and mic .CATEGORY_SET_ID = 1001 AND msib.segment1 in ('2

Query to Assign Items to Sourcing rule: --SET SERVEROUTPUT ON; DECLARE V_ASSIGNMENT_ID             MRP_SR_ASSIGNMENTS.ASSIGNMENT_ID%TYPE;  V_ASSIGNMENT_SET_ID    MRP_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID%TYPE := 1; V_ASSIGNMENT_TYPE       MRP_SR_ASSIGNMENTS.ASSIGNMENT_TYPE%TYPE := 1; V_SOURCING_RULE_ID      MRP_SR_ASSIGNMENTS.SOURCING_RULE_ID%TYPE := 1001; V_SOURCING_RULE_TYPE   MRP_SR_ASSIGNMENTS.SOURCING_RULE_TYPE%TYPE :=1; V_organization_id                    MRP_SR_ASSIGNMENTS.organization_id%TYPE; CURSOR C1 IS select msib.inventory_item_id,msib.segment1 ITEM_NUM,msib.organization_id    from mtl_system_items_b msib where msib.organization_id = 12   AND msib.segment1 in ('2017');  BEGIN   FOR SR IN C1 LOOP     V_organization_id := SR.organization_id ;      IF V_organization_id = 12 THEN             BEGIN                           V_ASSIGNMENT_ID := MRP_SR_ASSIGNMENTS_S.NEXTVAL ;                                    INSERT INTO MRP_SR_ASSIGNMENTS MSRA

Query to get Supplier data from Oracle R12 SELECT   aps.segment1 "SupplierNum",          assa.vendor_site_code "Address Book Identifier ",          aps.vendor_name "Supplier Company Name ",          --Hou.name "Operating Unit Name",          Assa.address_line1 "Supplier Address Line1",          Assa.address_line2 "Supplier Address Line2",          assa.address_line3 "Supplier Address Line3",          assa.address_line4 "Supplier Address Line4",          assa.city "Supplier city",          assa.state || assa.PROVINCE "Supplier State/Province",          assa.country "Supp_country_Code",          assa.zip "Supp_postal_Code",          apsite_rmt.ADDRESS_LINE1 "Supp_Remit_to_Address_Line_1",          apsite_rmt.ADDRESS_LINE2 "Supp_Remit_to_Address_Line_2",          apsite_rmt.ADDRESS_LINE3 "Supp_Remit_to_Address_Line_3",    

Query to get safety stock Quantity of inventory items: select msib.segment1,c.INVENTORY_ITEM_ID,SAFETY_STOCK_QUANTITY from mtl_safety_stocks c,  mtl_system_items_b msib   where c.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID     and c.ORGANIZATION_ID = msib.ORGANIZATION_ID     and c.SAFETY_STOCK_QUANTITY <> 0     and msib.ORGANIZATION_ID = 12     and msib.segment1 in ('98765'); Query to get the inventory item vendor and costs : select msi.inventory_item_id "Item ID", msi.segment1 "Part Number", msi.description  "Part Description - Internal", decode (msi.item_type,'XX_SUPPORT_ITEM','SUPPORT ITEM'                                        ,'XX_BULK_ITEM','BULK ITEM'                                        ,'P','PURCHASED ITEM'                                        ,'SA','SUBASSEMBLY'                                        ,'PH','PHANTOM ITEM'      

Query to get Tree structure of the indented BOM along with Vendor for the Purchased items and Item type SELECT DISTINCT LPAD (' ', LEVEL * 2) || LEVEL ORDER_LEVEL               , LPAD (' ', LEVEL * 1) || msib.segment1 ASSEMBLY_ITEM               , LPAD (' ', LEVEL * 4) || msib2.segment1 AS COMPONENT_ITEM               , msib2.description COMPONENT_ITEM_DESCRIPTION               , bic.bill_sequence_id c, bom.bill_sequence_id b                           , msib.description ASSEMBLY_DESCRIPTION               , msib.inventory_item_status_code ASSEMBLY_ITEM_STATUS                 , decode (msib2.PLANNING_MAKE_BUY_CODE, 1, 'MAKE'                                                     , 2, 'BUY'                        ,msib2.PLANNING_MAKE_BUY_CODE ) MAKE_BUY                         , decode (msib2.item_type,'SUPPORT_ITEM','SUPPORT ITEM'                                        ,'BULK_ITEM','BULK ITEM'            

Query to get PO & Receipt Data: select aps.segment1  "Supplier Number", poh.segment1  "Purchase Order Number", poh.AUTHORIZATION_STATUS  "Purchase Order Status", Null  "Purchase Order Release Number", poh.START_DATE  "Purchase Order Date", papf.full_name  "Buyer", msi.segment1   "Purchase Order Item Number", pll.closed_code  "Purchase Order Item Status", pol.PURCHASE_BASIS  "Purchase Order Item Type", msi.segment1  "Location Part Number", msi.description  "Location Part Description", pol.VENDOR_PRODUCT_NUM  "Supplier Part Number", POL.QUANTITY  "Item Order Quantity", pol.unit_price  "Location Item Price", poh.CURRENCY_CODE  "Location Currency Code", pol.unit_price  "Supplier Item Price", aps.PAYMENT_CURRENCY_CODE  "Supplier Currency Code", msi.PRIMARY_UNIT_OF_MEASURE  "Inventory Unit of Me

To update a data base column to a NULL value, you must enter the following values: Column Data Type        Required value to create NULL column For Char columns            char (12) For Date columns            TO_DATE (’1’,’j’) For Number columns       9.99E125 Ex: To update number column type to NULL Update po_headers set agent_id = 9.99E125 where agent_id = 101 and org_id = 100; Reference: How to Enter a Null Value (Clear a Value) For Any Column in Any BOM Interface Table [ID 264198.1]

## Profile Set Up To Trigger Receipt Traveler Report automatically upon creation of new Receipt. Navigation: Sysadmin-> Profile-> System-> Search for :  “ RCV: Print Receipt Traveler ” and set the value to Yes Enabling this profile will trigger  Receipt Traveler report as concurrent request after creation of new receipt. ## Profile option  Viewer: Text set to BROWSER at user level Enabling this profile option at user level will fix issue that occurs specific to user like unable to save transaction after clicking SAVE button.