PO Inventory sourcing query

  SELECT   DISTINCT
           c.name ORG,
           b.organization_code ORGANIZATION,
           poh.segment1 PO_NUMBER,
           poa.vendor_name VENDOR_NAME,
           poh.APPROVED_DATE,
           a.agent_name BUYER_NAME,
           msi.segment1 PART_NUMBER,
           msi.description,
           mc.description CATEGORY_CONCAT_SEGS,
           pol.unit_price PO_PRICE,
           poh.currency_code po_currency_code,
           pol.QUANTITY,
           pol.UNIT_MEAS_LOOKUP_CODE PO_UOM,
           cst.item_cost STD_COST,
           gsb.CURRENCY_CODE STD_COST_CURRENCY_CODE,
           poh.creation_date "PO ISSUE DATE",--,MMT.CURRENCY_CODE
           msi.INVENTORY_ITEM_STATUS_CODE ITEM_STATUS,--,rsh.creation_date "PO RECIEVE DATE"          
           (SELECT   DISTINCT MAX (h.creation_date)
              FROM   rcv_shipment_headers h, rcv_shipment_lines i
             WHERE   h.SHIPMENT_HEADER_ID = i.SHIPMENT_HEADER_ID
                     AND i.PO_LINE_ID = pol.PO_LINE_ID)
              "PO RECIEVE DATE",
           (SELECT   SUM (NVL (d.QUANTITY_RECEIVED, 0))
              FROM   rcv_shipment_lines d
             WHERE   pol.PO_LINE_ID = d.PO_LINE_ID)
              "RECIEVED QUANTITY"
    FROM   po_headers_all poh,
           po_lines_all pol,
           mtl_system_items_b msi,
           mtl_categories mc,
           CST_ITEM_COSTS cst,
           org_organization_definitions b,
           GL_SETS_OF_BOOKS gsb,
           po_vendors poa,
           po_agents_v a,
           hr_operating_units c
   WHERE       pol.po_header_id = poh.po_header_id
           AND poh.org_id = pol.org_id
           AND msi.inventory_item_id = pol.item_id
           AND mc.category_id = pol.category_id
           AND cst.INVENTORY_ITEM_ID = msi.inventory_item_id
           AND msi.organization_id = cst.organization_id
           AND poh.org_id IN (103, 235, 237)
           AND cst.cost_type_id = 3
           AND cst.item_cost <> 0
           AND msi.organization_id IN
                    (SELECT   organization_id
                       FROM   org_organization_definitions
                      WHERE   organization_code IN
                                    ('U01',
                                     'U06',
                                     'U08',
                                     'U09',
                                     'U13',
                                     'U19',
                                     'U20',
                                     'U21',
                                     'U23',
                                     'U26'))
           AND b.organization_id = msi.organization_id
           AND poa.vendor_id = poh.vendor_id
           AND a.agent_id = poh.agent_id
           AND c.ORGANIZATION_ID = poh.org_id
           --and poh.segment1='700017217'
           AND poh.creation_date BETWEEN '25-SEP-2013' AND '25-SEP-2014'
           /*and MMT.TRANSACTION_SOURCE_ID = poh.PO_HEADER_ID
                      and MMT.INVENTORY_ITEM_ID=msi.inventory_item_id
           and mmt.organization_id=cst.organization_id
           and mmt.currency_code is not null*/
           AND gsb.SET_OF_BOOKS_ID = b.SET_OF_BOOKS_ID
--and pll.PO_HEADER_ID=poh.po_header_id
--and pol.PO_LINE_ID=pll.PO_LINE_ID(+)
--and pol.PO_LINE_ID=pod.PO_LINE_ID(+)
--and pll.org_id=poh.org_id
--and pol.po_header_id=rsl.po_header_id
--and pol.PO_LINE_ID=rsl.PO_LINE_ID(+)
--and nvl(rsl.SHIPMENT_HEADER_ID,0)=nvl(rsh.SHIPMENT_HEADER_ID,0)
--and pll.LINE_LOCATION_ID=rsl.PO_LINE_LOCATION_ID
ORDER BY   poh.segment1,
           msi.segment1,
           c.name,
           b.organization_code

How to auto populate records by using custom.pll in oracle apps

If (form_name='OEXOEORD' and block_name='LINE')then

If (event_name='WHEN-NEW-BLOCK-INSTANCE') then

param_header_id := name_in('order.header_id');

param_order_type := name_in('order.order_type');

param_freight_terms := name_in('order.freight_terms');

BEGIN

SELECTCOUNT ( * )

INTO l_cnt

FROM oe_order_lines_all

WHERE header_id = param_header_id;

EXCEPTION

WHEN OTHERS

THEN

l_cnt

:= 1;

END;

BEGIN

SELECTCOUNT (DISTINCT x.lookup_type)

INTO l_cnt1

FROM(SELECT lookup_type

FROM fnd_lookup_values

WHERE lookup_type = 'AUTOPOPULATE_FREIGHT'

AND TAG = 'FREIGHT_TERM'

AND DESCRIPTION = param_freight_terms

AND NVL (END_DATE_ACTIVE, SYSDATE) <= SYSDATE

AND ENABLED_FLAG = 'Y') x,

(SELECT lookup_type

FROM fnd_lookup_values

WHERE lookup_type = 'AUTOPOPULATE_FREIGHT'

AND TAG = 'ORDER_TYPE'

AND meaning = param_order_type

AND NVL (END_DATE_ACTIVE, SYSDATE) <= SYSDATE

AND ENABLED_FLAG = 'Y') y

WHERE x.lookup_type = y.lookup_type;

EXCEPTION

WHEN OTHERS

THEN

l_cnt1

:= 0;

END;

if

(l_cnt < 1 and l_cnt1 > 0 ) then

OM_FREIGHTLINES_PRG

(param_header_id,param_order_type,param_freight_terms,fnd_global.user_id,fnd_global.resp_id,fnd_global.resp_appl_id);

-- do_key('ENTER_QUERY');

do_key('EXECUTE_QUERY');

end if;

end if;

end if;

Query to findout order type and freight terms

select ooh.order_number, ott.name , ooh.freight_terms_code, ol.meaning
from
oe_order_headers_all ooh,
OE_TRANSACTION_TYPES_TL ott,
oe_lookups ol
where ooh.order_type_id = ott.TRANSACTION_TYPE_ID
and upper (ol.lookup_type) = 'FREIGHT_TERMS'  and ol.enabled_flag = 'Y'
and upper (ol.lookup_code) = upper (ooh.freight_terms_code)
and ooh.header_id =p_header_id;

How To compile custom.pll in APPS

1. Login to unix server.
2. Navigate to /AU_TOP/resource.
3. Move the custom.pll to  /AU_TOP/resource.
4.run the below cmd:
f60gen module=CUSTOM.pll userid=username/password output_file=CUSTOM.plx module_type=library batch=no compile_all=special

How to findout database name

Select name from V$database;

Item Extract with sourcing rule and purchasing category

select distinct msi.segment1 "ITEM_NUMBER", 
msi.description,
ood.organization_code,
papf.full_name "BUYER",
msi.planner_code,
msa.sourcing_rule_name,
msi.list_price_per_unit,
mcv.description "PURCHASING_CATEGORY",
--mcv.structure_name,
msi.inventory_item_status_code,
msi.preprocessing_lead_time,
msi.full_lead_time,
msi.postprocessing_lead_time
from mtl_system_items_b msi,
org_organization_definitions ood,
per_all_people_f papf,
mrp_sr_assignments_v msa,
mtl_item_categories mic,
mtl_categories_v mcv
where msi.organization_id = ood.organization_id
and msi.buyer_id = papf.person_id (+)
and msi.inventory_item_id = msa.inventory_item_id(+)
and msi.organization_id = msa.organization_id(+)
and mic.organization_id=msi.organization_id
and mic.inventory_item_id = msi.inventory_item_id
and mic.category_id = mcv.category_id
and mcv.structure_name ='ENVS PO Categories'
and msi.inventory_item_status_code ='Active';
--and msi.segment1 ='08180813';

How to add a new line to an existing sales order using an API OE_ORDER_PUB.PROCESS_ORDER

SET SERVEROUTPUT ON;
DECLARE
v_api_version_number           NUMBER  := 1;
v_return_status                VARCHAR2 (2000);
v_msg_count                    NUMBER;
v_msg_data                     VARCHAR2 (2000);

 
-- IN Variables --
v_header_rec                   oe_order_pub.header_rec_type;
v_line_tbl                     oe_order_pub.line_tbl_type;
v_action_request_tbl           oe_order_pub.request_tbl_type;
v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;

 
-- OUT Variables --
v_header_rec_out               oe_order_pub.header_rec_type;
v_header_val_rec_out           oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out                 oe_order_pub.line_tbl_type;
v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out       oe_order_pub.request_tbl_type;

 
v_msg_index                    NUMBER;
v_data                         VARCHAR2 (2000);
v_loop_count                   NUMBER;
v_debug_file                   VARCHAR2 (200);
b_return_status                VARCHAR2 (200);
b_msg_count                    NUMBER;
b_msg_data                     VARCHAR2 (2000);
BEGIN

 
DBMS_OUTPUT.PUT_LINE('Starting of script');

 
-- Setting the Enviroment --

 
--mo_global.init('ONT');
fnd_global.apps_initialize ( user_id      => 10688
                            ,resp_id      => 50692
                            ,resp_appl_id => 660);
--mo_global.set_policy_context('S',83);
begin
dbms_application_info.set_client_info('103');
end;

 
v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;

 
-- Line Record --
v_line_tbl (1)                      := oe_order_pub.g_miss_line_rec;
v_line_tbl (1).operation            := oe_globals.g_opr_create;
v_line_tbl (1).header_id            := 157397; -- Existing order header id
v_line_tbl (1).inventory_item_id    := 862088;
v_line_tbl (1).ordered_quantity     := 1;
v_line_tbl (1).unit_selling_price   := 1;
--v_line_tbl (1).calculate_price_flag := 'Y';

 
DBMS_OUTPUT.PUT_LINE('Starting of API');

 
-- Calling the API to add a new line to an existing Order --

 
OE_ORDER_PUB.PROCESS_ORDER (
p_api_version_number            => v_api_version_number
, p_header_rec                  => v_header_rec
, p_line_tbl                    => v_line_tbl
, p_action_request_tbl          => v_action_request_tbl
, p_line_adj_tbl                => v_line_adj_tbl
-- OUT variables
, x_header_rec                  => v_header_rec_out
, x_header_val_rec              => v_header_val_rec_out
, x_header_adj_tbl              => v_header_adj_tbl_out
, x_header_adj_val_tbl          => v_header_adj_val_tbl_out
, x_header_price_att_tbl        => v_header_price_att_tbl_out
, x_header_adj_att_tbl          => v_header_adj_att_tbl_out
, x_header_adj_assoc_tbl        => v_header_adj_assoc_tbl_out
, x_header_scredit_tbl          => v_header_scredit_tbl_out
, x_header_scredit_val_tbl      => v_header_scredit_val_tbl_out
, x_line_tbl                    => v_line_tbl_out
, x_line_val_tbl                => v_line_val_tbl_out
, x_line_adj_tbl                => v_line_adj_tbl_out
, x_line_adj_val_tbl            => v_line_adj_val_tbl_out
, x_line_price_att_tbl          => v_line_price_att_tbl_out
, x_line_adj_att_tbl            => v_line_adj_att_tbl_out
, x_line_adj_assoc_tbl          => v_line_adj_assoc_tbl_out
, x_line_scredit_tbl            => v_line_scredit_tbl_out
, x_line_scredit_val_tbl        => v_line_scredit_val_tbl_out
, x_lot_serial_tbl              => v_lot_serial_tbl_out
, x_lot_serial_val_tbl          => v_lot_serial_val_tbl_out
, x_action_request_tbl          => v_action_request_tbl_out
, x_return_status               => v_return_status
, x_msg_count                   => v_msg_count
, x_msg_data                    => v_msg_data
);

 
DBMS_OUTPUT.PUT_LINE('Completion of API');

 


IF v_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Line Addition to Existing Order Success ');
ELSE
    DBMS_OUTPUT.put_line ('Line Addition to Existing Order failed:'||v_msg_data);
    ROLLBACK;
    FOR i IN 1 .. v_msg_count
    LOOP
      v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| v_msg_data);
    END LOOP;
END IF;
END;
/