Create routing and sequence using interface table

There are two Routing Interface tables, which needs to be populated in order to create routings.

bom_op_routing_interface
bom_op_sequences_interface

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

Once data is successfully loaded, then call the standard oracle concurrent program : Bill and Routing Interface to upload the data from interface tables to Base tables.

Create BOM and component by using interface tables

There are two BOM Interface tables, which needs to be populated in order to create BOM.

BOM_BILL_OF_MTLS_INTERFACE
BOM_INVENTORY_COMPS_INTERFACE
Insert command :
            INSERT INTO bom_bill_of_mtls_interface(
                          assembly_item_id
                              ,organization_id
                              ,bill_sequence_id
                              ,item_number
                              ,item_description
                              ,last_update_date
                              ,last_updated_by
                              ,creation_date
                              ,created_by
                              ,last_update_login
                              ,transaction_type
                              ,process_flag
                               )
            VALUES( <assembly_item_id>
                             ,<organization_id>
                             ,<bill_sequence_id>
                             ,<assembly_item_number>
                             ,<description>
                             ,SYSDATE
                              ,<user_id>
                              ,SYSDATE
                              ,user_id
                              ,user_id
                         ,<CREATE or UPDATE> -- If it is update, then bill sequence id is required, else NULL
                              ,1
                             );             
             INSERT INTO BOM_INVENTORY_COMPS_INTERFACE
                                (  component_item_id
                                    ,assembly_item_id
                                    ,organization_id
                                    ,item_num
                                    ,operation_seq_num
                                    ,effectivity_date
                                    ,transaction_type
                                    ,process_flag
                                    ,bill_sequence_id
                                    ,component_sequence_id
                                    ,item_description
                                    ,basis_type
                                    ,component_quantity
                                    ,component_yield_factor
                                    ,implementation_date
                                    ,supply_subinventory
                                    ,wip_supply_type
                                    ,so_basis
                                    ,check_atp
                                    ,planning_factor
                                    ,low_quantity
                                    ,high_quantity
                                    ,last_update_date
                                    ,last_updated_by
                                    ,creation_date
                                    ,created_by
                                    ,last_update_login
                                    ,component_remarks
                                     )
                              values
                                    (
                                     <component_item_id>
                                    ,<assembly_item_id>
                                    ,<organization_id>
                                    ,<item_seq_num>
                                    ,<operation_sequence>
                                    ,SYSDATE
                                    ,<CREATE or UPDATE> -- If it is update, then Component sequence id is required, else NULL
                                    ,1
                                    ,<bill_sequence_id>  -- If transaction type is UPDATE, then this value is required else NULL
                                    ,<comp_sequence_id> -- If transaction type is UPDATE, then this value is required else NULL
                                    ,<com_description>
                                    ,DECODE(basis_type,1,NULL,2)
                                    ,<component_quantity>
                                    ,<component_yield_factor>
                                    ,<implementation_date>
                                    ,<supply_subinventory>
                                    ,<l_wip_supply_type> -- lookup code from MFG_LOOKUPS where lookup_type = 'WIP_SUPPLY'
                                    ,<so_basis>
                                    ,DECODE(check_atp,'Y',1,'N',2)
                                    ,<planning_factor>
                                    ,<low_quantity>
                                    ,<high_quantity>
                                    ,SYSDATE
                                    ,user_id
                                    ,SYSDATE
                                    ,user_id
                                    ,user_id
                                   ,'BOM Migration'
                                    );
Once data is successfully loaded, then call the standard oracle concurrent program : Bill and Routing Interface to upload the data from interface tables to Base tables.

API to create BOM and component

bom_bo_pub.Process_Bom

What are the possible reasons for Interface trip stop to complete with Error?

Few reasons may be

1) Order is on Hold

2) Tax is not applied properly

3) On-Hand qty is not available in the Inventory

4) Inventory Period is not open

5) Make sure Schedule Ship Date or Requested Date is within the range of order Date

What is Interface Trip Stop?

ITS is triggered at the time of shipping if the option ‘defer interface’ is not checked (By Default this check box is uncheck only). When this report is run, it performs two main things:

Update the order management (OE_ORDER_LINES_ALL)

Trigger the inventory interface (TO UPDATE Inventory tables)

In First part it make update on oe_order_lines_all table as well as wsh_delivery_details and once this part executed successfully only then SECOND phase of ITS has triggered. If FIRST part error out for some reason then 2nd part will not be triggered, and even if you try to submit? Inventory Interface?, it will not pick up you data. Reason ? it will validate and check if oe_interfaced_flag in wsh_delivery_details table is Y or not. If it is N or X ?inventory Interface? will not pick that record for processing.

Query to find all level components of an Assembly

select
(select msi.segment1 from mtl_system_items msi where msi.inventory_item_id=bom.assembly_item_id and msi.organization_id=402) parent_item,
bom.assembly_item_id,
lpad(' ',2*(level-1),' ')||
(select msi.segment1 from mtl_system_items msi where msi.inventory_item_id=bic.component_item_id and msi.organization_id=402) child_item,
bic.component_item_id                  child_item_id,
--bic.component_sequence_id              ,
bic.bill_sequence_id                   ,
--parent_bill_seq_id             ,
bic.operation_seq_num                  ,
--bic.bom_item_type                  ,
--bic.item_num                           ,
level,
bic.component_quantity
from
bom_inventory_components bic,
(select * from bom_bill_of_materials where organization_id=402) bom
where bom.bill_sequence_id=bic.bill_sequence_id
start with bom.assembly_item_id=1040442
connect by  prior bic.component_item_id=bom.assembly_item_id
--order by level, bom.assembly_item_id

API for creating, updating and deleting Pricelist?

qp_price_list_pub.process_price_list

AR Receipt Payment Data Query

SELECT DISTINCT hou.name "Operating Unit Name",
--hou.ORGANIZATION_ID,
  TRUNC (acra.receipt_date) "Receipt Date",
  acra.receipt_number "Receipt Number",
 rcta.trx_number "Invoice Number",
 -- rcta.org_id,
  decode (ara.status,
  'ACC', 'On-Account',
  'APP', 'Applied',
  'UNID', 'Unidentified',
  'UNAPP', 'Unapplied',
  'ACTIVITY', 'ACTIVITY', 'None' ) "Receipt Status",
  hcas.account_number "Customer Number",
  hp.party_name "Customer Name",
  hl1.state "Customer Ship-to State" ,
  hl1.country "Country",
  --apsa.amount_due_remaining
 -- apsa.amount_due_original "Receipt Amount",
  acra.amount "Receipt Amount",
  ara.AMOUNT_APPLIED,
  --apsa.Amount_line_items_original,
  --apsa.tax_original,
  ara.line_applied,
  ara.tax_applied
FROM ar_cash_receipts_all acra,
  ar_receivable_applications_all ara,
  ra_customer_trx_all rcta,
  ar_payment_schedules_all apsa,
  hz_parties hp ,
  hz_cust_accounts hcas ,
  hz_cust_acct_sites_all hcasa ,
  hz_party_sites hps ,
  hz_contact_points hcp ,
  hz_locations hl,
  hr_organization_units hou,
  apps.hz_cust_site_uses_all hcsua1   ,
  apps.hz_cust_acct_sites_all hcasa1   ,
  apps.hz_party_sites hps1             ,
  apps.hz_locations hl1               
WHERE acra.pay_from_customer = hcas.cust_account_id(+)
AND hcas.party_id            = hp.party_id(+)
AND hp.party_id              = hps.party_id
AND hcasa.party_site_id      = hps.party_site_id
AND hcasa.status             = 'A'
AND hps.status               = 'A'
AND hps.party_site_id        = hcp.owner_table_id(+)
AND hcp.contact_point_type   = 'PHONE'
AND hcp.owner_table_name     = 'HZ_PARTY_SITES'
AND hcp.phone_line_type      = 'GEN'
AND hcp.status               = 'A'
AND hps.location_id          = hl.location_id
AND hcasa.org_id             = hou.organization_id
AND hcasa.org_id             = acra.org_id
AND hcasa.org_id             = ara.org_id
AND hcasa.org_id             = rcta.org_id
AND hcasa.org_id             = apsa.org_id
  --AND hcasa.org_id IN (103,235,237,221)
--AND hcasa.org_id               =103
AND ara.cash_receipt_id        =acra.cash_receipt_id
AND ara.applied_customer_trx_id=rcta.customer_trx_id
AND rcta.customer_trx_id       = apsa.customer_trx_id
--AND acra.receipt_number        ='13076687'
AND rcta.ship_to_site_use_id = hcsua1.site_use_id
AND hcsua1.cust_acct_site_id  = hcasa1.cust_acct_site_id
AND hcasa1.party_site_id      = hps1.party_site_id
AND hps1.location_id          = hl1.location_id
AND rcta.org_id = hcsua1.org_id
AND rcta.org_id = hcasa1.org_id
AND to_date((to_char(trunc(acra.receipt_date),'dd-mon-rrrr') ),'dd-mon-rrrr')
BETWEEN to_date('01-Oct-2014','dd-mon-rrrr') and to_date('31-Dec-2014','dd-mon-rrrr')
--AND hl.country ='US'
ORDER BY 2,5,3; 

Query to find account details for item (link between MTL_SYSTEM_ITEMS_FVL and gl_code_combinations)

/* Formatted on 30-01-2015 20:40:51 (QP5 v5.114.809.3010) */
  SELECT   ood.OPERATING_UNIT,
           hou.name OU_NAME,
           ood.ORGANIZATION_CODE,
           ood.ORGANIZATION_NAME,
           msi.segment1 ITEM_NUMBER,
           msi.ITEM_TYPE,
           msi.INVENTORY_ITEM_STATUS_CODE,
              --COST_OF_SALES_ACCOUNT,
              gcc.segment1
           || '.'
           || gcc.segment2
           || '.'
           || gcc.segment3
           || '.'
           || gcc.segment4
           || '.'
           || gcc.segment5
           || '.'
           || gcc.segment6
           || '.'
           || gcc.segment7
           || '.'
           || gcc.segment8
              "COST_ACCOUNT",
              gcc_exp.segment1
           || '.'
           || gcc_exp.segment2
           || '.'
           || gcc_exp.segment3
           || '.'
           || gcc_exp.segment4
           || '.'
           || gcc_exp.segment5
           || '.'
           || gcc_exp.segment6
           || '.'
           || gcc_exp.segment7
           || '.'
           || gcc_exp.segment8
              "EXPENSE_ACCOUNT",
              gcc_s.segment1
           || '.'
           || gcc_s.segment2
           || '.'
           || gcc_s.segment3
           || '.'
           || gcc_s.segment4
           || '.'
           || gcc_s.segment5
           || '.'
           || gcc_s.segment6
           || '.'
           || gcc_s.segment7
           || '.'
           || gcc_s.segment8
              "SALES_ACCOUNT"
    FROM   MTL_SYSTEM_ITEMS_FVL msi,
           gl_code_combinations gcc,
           gl_code_combinations gcc_exp,
           gl_code_combinations gcc_s,
           org_organization_definitions ood,
           hr_operating_units hou
   WHERE       msi.ORGANIZATION_ID = ood.ORGANIZATION_ID
           AND ood.OPERATING_UNIT = hou.ORGANIZATION_ID
           AND msi.COST_OF_SALES_ACCOUNT = gcc.CODE_COMBINATION_ID(+)
           AND msi.EXPENSE_ACCOUNT = gcc_exp.CODE_COMBINATION_ID(+)
           AND msi.SALES_ACCOUNT = gcc_s.CODE_COMBINATION_ID(+)
           AND msi.INVENTORY_ITEM_STATUS_CODE = 'Active'
-- and msi.segment1 ='08661137'--INVENTORY_ITEM_ID ='22143'
ORDER BY   ood.OPERATING_UNIT, ood.ORGANIZATION_CODE, msi.segment1

Query to find items base on OE categories

SELECT   distinct                                                -- mc.category_id,
           mc .description,
           mc.attribute_category,
           ood.OPERATING_UNIT,
           hou.name OU_NAME,
           ood.ORGANIZATION_CODE,
           ood.ORGANIZATION_NAME,
           msi.segment1,
           msi.DESCRIPTION "ITEM_DESCRIPTION"
    FROM   MTL_CATEGORIES mc,
           MTL_ITEM_CATEGORIES mic,
           mtl_system_items_b msi,
           org_organization_definitions ood,
           hr_operating_units hou
   WHERE       mc.category_id = mic.category_id
           AND mic.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
           AND mic.ORGANIZATION_ID = msi.ORGANIZATION_ID
           AND mic.ORGANIZATION_ID = ood.ORGANIZATION_ID
           AND msi.ORGANIZATION_ID = ood.ORGANIZATION_ID
           and ood.OPERATING_UNIT =  hou.ORGANIZATION_ID
           AND mc.attribute_category = 'ENVS OE Categories'
           AND mc.description IN
                    ('Mem Filt Sep.Membrane Film.Polymer Treated.Super Critical CO2.Oleophobic Grade 8.All',
                     'ESP Electrical.AVC''s.All.All.All.All')

API to delete the Price List (qp_price_list_pub.process_price_list)

CREATE OR REPLACE PROCEDURE APPS.geps_price_list_delete_sql
(
errbuf                      OUT VARCHAR2
,retcode                   OUT NUMBER
)
as
cursor rout_det is
select
*
from
geps_price_list_stg
where process_flag=1;
   gpr_return_status                                 VARCHAR2 (1) := NULL;
   gpr_msg_count                                     NUMBER := 0;
   gpr_msg_data                                      VARCHAR2 (2000);
   gpr_price_list_rec                                qp_price_list_pub.price_list_rec_type;
   gpr_price_list_val_rec                            qp_price_list_pub.price_list_val_rec_type;
   gpr_price_list_line_tbl                           qp_price_list_pub.price_list_line_tbl_type;
   gpr_price_list_line_val_tbl                       qp_price_list_pub.price_list_line_val_tbl_type;
   gpr_qualifiers_tbl                                qp_qualifier_rules_pub.qualifiers_tbl_type;
   gpr_qualifiers_val_tbl                            qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   gpr_pricing_attr_tbl                              qp_price_list_pub.pricing_attr_tbl_type;
   gpr_pricing_attr_val_tbl                          qp_price_list_pub.pricing_attr_val_tbl_type;
   ppr_price_list_rec                                qp_price_list_pub.price_list_rec_type;
   ppr_price_list_val_rec                            qp_price_list_pub.price_list_val_rec_type;
   ppr_price_list_line_tbl                           qp_price_list_pub.price_list_line_tbl_type;
   ppr_price_list_line_val_tbl                       qp_price_list_pub.price_list_line_val_tbl_type;
   ppr_qualifiers_tbl                                qp_qualifier_rules_pub.qualifiers_tbl_type;
   ppr_qualifiers_val_tbl                            qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   ppr_pricing_attr_tbl                              qp_price_list_pub.pricing_attr_tbl_type;
   ppr_pricing_attr_val_tbl                          qp_price_list_pub.pricing_attr_val_tbl_type;
   k                                                 NUMBER := 1;
   j                                                 NUMBER := 1;
   v_list_line_id qp_list_lines_v.LIST_LINE_ID%type;
     l_item_id   varchar2(50);
  l_header_id   Number;
  v_err_msg varchar2(500);
  v_count number;
BEGIN

 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start program');
For c in rout_det
Loop
begin
select inventory_item_id
into
l_item_id
from mtl_system_items_b
where segment1=c.Product_value
and organization_id=104;
select list_header_id into l_header_id from qp_list_headers
where name=c.Price_List_Name  ;
begin
select LIST_LINE_ID into v_list_line_id
from qp_list_lines_v
where LIST_HEADER_ID=l_header_id
and product_attr_value=l_item_id;
exception
when no_data_found then
v_list_line_id := null;
update geps_price_list_stg
set process_flag=4,
error_message='no_data_found in pricelist'
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;
commit;
when others then
v_list_line_id := null;
end;

 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start program1');

if v_list_line_id is not null then
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start program2');
   oe_debug_pub.initialize;
   oe_debug_pub.setdebuglevel (5);
   oe_msg_pub.initialize;
--dbms_output.put_line('after get price list ');
   /* setup the list_header rec for update */
   gpr_price_list_rec.list_header_id                        := l_header_id;
   gpr_price_list_rec.NAME                                  := c.Price_List_Name;
   gpr_price_list_rec.list_type_code                        := 'PRL';
   --gpr_price_list_rec.description                           := '<price_list_description>';
   gpr_price_list_rec.operation                             := qp_globals.g_opr_update;
-- delete the price list line rec
   gpr_price_list_line_tbl (k).list_header_id               := l_header_id;
   gpr_price_list_line_tbl (k).list_line_id                 := v_list_line_id;
   gpr_price_list_line_tbl (k).list_line_type_code          := 'PLL';
   gpr_price_list_line_tbl (k).operation                    := qp_globals.g_opr_delete;
--dbms_output.put_line('before process price list ');
   qp_price_list_pub.process_price_list (p_api_version_number          => 1
                                       , p_init_msg_list               => fnd_api.g_false
                                       , p_return_values               => fnd_api.g_false
                                       , p_commit                      => fnd_api.g_false
                                       , x_return_status               => gpr_return_status
                                       , x_msg_count                   => gpr_msg_count
                                       , x_msg_data                    => gpr_msg_data
                                       , p_price_list_rec              => gpr_price_list_rec
                                       , p_price_list_line_tbl         => gpr_price_list_line_tbl
                                       , p_pricing_attr_tbl            => gpr_pricing_attr_tbl
                                       , x_price_list_rec              => ppr_price_list_rec
                                       , x_price_list_val_rec          => ppr_price_list_val_rec
                                       , x_price_list_line_tbl         => ppr_price_list_line_tbl
                                       , x_price_list_line_val_tbl     => ppr_price_list_line_val_tbl
                                       , x_qualifiers_tbl              => ppr_qualifiers_tbl
                                       , x_qualifiers_val_tbl          => ppr_qualifiers_val_tbl
                                       , x_pricing_attr_tbl            => ppr_pricing_attr_tbl
                                       , x_pricing_attr_val_tbl        => ppr_pricing_attr_val_tbl
                                        );

    IF gpr_return_status = fnd_api.g_ret_sts_success THEN
      COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Item deleted from the price list is Sucessfull');
update geps_price_list_stg
set process_flag=2,
error_message='Success'
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;
commit;
    ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Item deleted from the price list Failed');
      ROLLBACK;
   update geps_price_list_stg
set process_flag=3,
error_message='Error'
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;
commit;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ss6 ');
end if;
exception when others then
v_err_msg :=SQLERRM;
update geps_price_list_stg
set process_flag=3,
error_message=v_err_msg
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;

end;
end loop;
end;
/

Which programs will kick at the time of ship confirmed?

Interface Trip Stop
Commercial Invoice
Packing Slip Report
Bill of Lading

Which programs will kick at the time of Launch Pick Release?

Pick Slip Report

Shipping Exception Report

Auto Pack Report