SELECT
ph.SEGMENT1 po_num
, ph.CREATION_DATE
, hou.name “Operating Unit”
, ppx.full_name “Buyer Name”
, ph.type_lookup_code “PO Type”
, plc.displayed_field “PO Status”
, ph.COMMENTS
, pl.line_num
, plt.order_type_lookup_code “Line Type”
, NULL “Item Code”
, pl.item_description
, pl.unit_meas_lookup_code “UOM”
, pl.base_unit_price
, pl.unit_price
, pl.quantity
, ood.organization_code “Shipment Org Code”
, ood.organization_name “Shipment Org Name”
, pv.vendor_name supplier
, pvs.vendor_site_code
, (pl.unit_price * pl.quantity) “Line Amount”
, prh.segment1 req_num
, prh.type_lookup_code req_method
, ppx1.full_name “Requisition requestor”
FROM po_headers_all ph
, po_lines_all pl
, po_distributions_all pda
, po_vendors pv
, po_vendor_sites_all pvs
, po_distributions_all pd
, po_req_distributions_all prd
, po_requisition_lines_all prl
, po_requisition_headers_all prh
, hr_operating_units hou
, per_people_x ppx
, po_line_types_b plt
, org_organization_definitions ood
, per_people_x ppx1
, po_lookup_codes plc
WHERE
1=1
AND TO_CHAR(ph.creation_date, ‘YYYY’) IN (2010, 2011)
AND ph.vendor_id = pv.vendor_id
AND ph.po_header_id = pl.po_header_id
AND ph.vendor_site_id = pvs.vendor_site_id
AND ph.po_header_id = pd.po_header_id
and pl.po_line_id = pd.po_line_id
AND pd.req_distribution_id = prd.distribution_id (+)
AND prd.requisition_line_id = prl.requisition_line_id (+)
AND prl.requisition_header_id = prh.requisition_header_id (+)
and hou.organization_id = ph.org_id
and ph.agent_id = ppx.person_id
and pda.po_header_id = ph.po_header_id
and pda.po_line_id = pl.po_line_id
and pl.line_type_id = plt.line_type_id
and ood.organization_id = pda.destination_organization_id
and ppx1.person_id (+) = prh.preparer_id
and plc.lookup_type = ‘DOCUMENT STATE’
and plc.LOOKUP_CODE = ph.closed_code
and pl.item_id is null
UNION
— Purchase Orders for inventory items
SELECT
ph.SEGMENT1 po_num
, ph.CREATION_DATE
, hou.name “Operating Unit”
, ppx.full_name “Buyer Name”
, ph.type_lookup_code “PO Type”
, plc.displayed_field “PO Status”
, ph.COMMENTS
, pl.line_num
, plt.order_type_lookup_code “Line Type”
, msi.segment1 “Item Code”
, pl.item_description
, pl.unit_meas_lookup_code “UOM”
, pl.base_unit_price
, pl.unit_price
, pl.quantity
, ood.organization_code “Shipment Org Code”
, ood.organization_name “Shipment Org Name”
, pv.vendor_name supplier
, pvs.vendor_site_code
, (pl.unit_price * pl.quantity) “Line Amount”
, prh.segment1 req_num
, prh.type_lookup_code req_method
, ppx1.full_name “Requisition requestor”
FROM po_headers_all ph
, po_lines_all pl
, po_distributions_all pda
, po_vendors pv
, po_vendor_sites_all pvs
, po_distributions_all pd
, po_req_distributions_all prd
, po_requisition_lines_all prl
, po_requisition_headers_all prh
, hr_operating_units hou
, per_people_x ppx
, mtl_system_items_b msi
, po_line_types_b plt
, org_organization_definitions ood
, per_people_x ppx1
, po_lookup_codes plc
WHERE
1=1
AND TO_CHAR(ph.creation_date, ‘YYYY’) IN (2010, 2011)
AND ph.vendor_id = pv.vendor_id
AND ph.po_header_id = pl.po_header_id
AND ph.vendor_site_id = pvs.vendor_site_id
AND ph.po_header_id = pd.po_header_id
and pl.po_line_id = pd.po_line_id
AND pd.req_distribution_id = prd.distribution_id (+)
AND prd.requisition_line_id = prl.requisition_line_id (+)
AND prl.requisition_header_id = prh.requisition_header_id (+)
and hou.organization_id = ph.org_id
and ph.agent_id = ppx.person_id
and pda.po_header_id = ph.po_header_id
and pda.po_line_id = pl.po_line_id
and pl.line_type_id = plt.line_type_id
and ood.organization_id = pda.destination_organization_id
and ppx1.person_id (+) = prh.preparer_id
and pda.destination_organization_id = msi.organization_id (+)
and msi.inventory_item_id = nvl(pl.item_id, msi.inventory_item_id)– OR pl.item_id is null)
and plc.lookup_type = ‘DOCUMENT STATE’
and plc.LOOKUP_CODE = ph.closed_code
and pl.item_id is not null
Receiving transactions with PO and requisition information
SELECT
ph.segment1 po_num,
ood.organization_name,
pol.po_line_id,
pll.quantity,
rsh. receipt_source_code,
rsh. vendor_id,
rsh. vendor_site_id,
rsh. organization_id,
rsh. shipment_num,
rsh. receipt_num,
rsh. ship_to_location_id,
rsh. bill_of_lading,
rsl.shipment_line_id,
rsl.QUANTITY_SHIPPED,
rsl.QUANTITY_RECEIVED ,
rct.transaction_type,
rct.transaction_id,
nvl(rct.source_doc_quantity,0) transaction_qty
from rcv_transactions rct
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines_all pol
, po_line_locations_all pll
, po_headers_all ph
, org_organization_definitions ood
where 1=1
and to_char(rct.creation_date, ‘YYYY’) in (‘2010’, ‘2011’)
and rct.po_header_id = ph.po_header_id
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and rct.shipment_line_id=rsl.shipment_line_id
and rsl.shipment_header_id=rsh.shipment_header_id
and rsh.ship_to_org_id = ood.organization_id
order by rct.transaction_id
How to Implement Bar Code in XML Publisher Report
1) Copy Barcode font in C:WindowsFonts directory.
2) Copy xdo.cfg in C:Program FilesOracleXML Publisher DesktopTemplate Builder for Wordconfig directory. The sample file is already available when BI Publisher desktop is installed.
3) Right click the font file and go to properties. Copy the .ttf file name. Double click the font file to get the family name.
4) Open the xdo.cfg file and paste as follows (My filename is w39elc.ttf and font family is WASP 39 ELC)
Share this:
R12 – Adding Descriptive Flex Field (DFF) through OAF Personalization
Name -> XXCUST_SUPPLIER_DFF
Title -> XXCUST – Supplier DFF
Table Name -> AP_SUPPLIERS
DFV View name -> XXCUST_SUPPLIER_DFV
Reference Fields -> ATTRIBUTE_CATEGORY
Value Set -> XXCUST_SUP_TYPE ( Values : External and Internal )
Reference Field -> ATTRIBUTE_CATEGORY
Share this:
Query to get PO Requisition, PO and Receipt details in R12
Share this:
R12 – Ship Confirm using API
1. Back ordering unspecified quantities
2. Closing the delivery automatically by submitting the Trip stop program after ship confirm is successful
CREATE OR REPLACE PROCEDURE erps_ship_confirm_delivery (
v_delivery_name IN VARCHAR2, — delivery number
v_action IN VARCHAR2, — Pass ‘B’ to backorder the unspecified quantity
p_ship_conf_status OUT VARCHAR2,
x_msg_data OUT VARCHAR2)
IS
p_api_version_number NUMBER;
init_msg_list VARCHAR2 (30);
x_msg_count NUMBER;
x_msg_details VARCHAR2 (32000);
x_msg_summary VARCHAR2 (32000);
p_validation_level NUMBER;
p_commit VARCHAR2 (30);
x_return_status VARCHAR2 (15);
source_code VARCHAR2 (15);
changed_attributes wsh_delivery_details_pub.changedattributetabtype;
p_action_code VARCHAR2 (15);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2 (30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2 (30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2 (30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2 (10);
p_sc_close_trip_flag VARCHAR2 (10);
p_defer_iface VARCHAR2 (10);
p_sc_create_bol_flag VARCHAR2 (10);
p_sc_stage_del_flag VARCHAR2 (10);
p_sc_trip_ship_method VARCHAR2 (30);
p_sc_actual_dep_date VARCHAR2 (30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2 (60);
p_wv_override_flag VARCHAR2 (10);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
p_msg_data VARCHAR2 (32000);
fail_api EXCEPTION;
BEGIN
x_return_status := wsh_util_core.g_ret_sts_success;
p_action_code := ‘CONFIRM’;
p_delivery_name := v_delivery_name;
p_sc_action_flag := v_action;
p_sc_close_trip_flag := ‘Y’; — Trip stop concurrent program will be submitted automatically
p_defer_iface := ‘N’;
wsh_deliveries_pub.
delivery_action (p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => p_msg_data,
p_action_code => p_action_code,
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_close_trip_flag => p_sc_close_trip_flag,
p_sc_create_bol_flag => p_sc_create_bol_flag,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_sc_defer_interface_flag => p_defer_iface,
p_wv_override_flag => p_wv_override_flag,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name);
IF (x_return_status != wsh_util_core.g_ret_sts_success)
THEN
wsh_util_core.get_messages (‘Y’,
x_msg_summary,
x_msg_details,
x_msg_count);
IF x_msg_count > 1
THEN
x_msg_data := x_msg_summary || x_msg_details;
ELSE
x_msg_data := x_msg_summary;
END IF;
p_ship_conf_status := ‘E’;
ELSE
p_ship_conf_status := ‘S’;
END IF;
END erps_ship_confirm_delivery;
SHIP CONFIRMATION THROUGH FORMS
Navigate to Shipping responsibility >> Shipping >> Transactions
Query the delivery that need to be ship confirmed
click ship confirm button.
Share this:
R12 – Apps Menu and Functions Related Scripts
SELECT DISTINCT
fmep.menu_id,
DECODE (
fmep.function_id,
NULL, DECODE (
fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, ‘No Func’,
fmec1.function_id),
fmec.function_id),
fmep.function_id)
funcID,
fff.user_function_name,
fff.description
FROM fnd_form_functions_tl fff,
fnd_menu_entries fmec1,
fnd_menu_entries fmec,
fnd_menu_entries fmep
WHERE fmep.menu_id = (SELECT menu_id
FROM fnd_menus
WHERE menu_name = ‘INV_NAVIGATE’ –Change the menu according to your requirement
AND ROWNUM = 1)
AND fmep.sub_menu_id = fmec.menu_id(+)
AND fmec.sub_menu_id = fmec1.menu_id(+)
AND fff.function_id =
DECODE (
fmep.function_id,
NULL, DECODE (
fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, -999,
fmec1.function_id),
fmec.function_id),
fmep.function_id)
ORDER BY DECODE (
fmep.function_id,
NULL, DECODE (
fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, ‘No Func’,
fmec1.function_id),
fmec.function_id),
fmep.function_id)
Share this: