1. Overview

This document talks about getting the sales details of the item in oracle apps r12

2.Technologies and Tools Used

The following technologies have been used to achieve the expected output.

  • SQL Code

3.Use Case

This report is used on an everyday basis by the customer to check the item sales margin details.

This document gives you the SQL query to fetch the details.

4. Architecture

Step 1:

Run this code package:

create or replace PACKAGE xxa_pkgutil AS
—
/**********************************************************************************************
* OBJECT TYPE : Package specification *
* OBJECT NAME : xxa_pkgutil *
* PURPOSE : This is a utility package to access minor information *
* *
* Author Version Date Description *
* ————- ——- ———— —————————————————— *
* Jegan 1.0 16-DEC-2022 Intial version ** *
***********************************************************************************************/
–create or replace TYPE TY_CHARTBLLIST IS TABLE OF VARCHAR2(50); –nested
g_uom_th NUMBER := 1000;
FUNCTION sflist (
p_str VARCHAR2,
p_separator VARCHAR2
) RETURN ty_chartbllist;
FUNCTION sfgetporemainingqty (
p_item_id po_lines_all.item_id%TYPE,
p_ship_to_location_id po_line_locations_all.ship_to_location_id%TYPE
) RETURN NUMBER
RESULT_CACHE;
FUNCTION sfgetlotcreationdate (
p_lot_number mtl_lot_numbers.lot_number%TYPE
) RETURN DATE
RESULT_CACHE;
FUNCTION sfgetlotdffdate (
p_lot_number mtl_lot_numbers.lot_number%TYPE
) RETURN DATE
RESULT_CACHE;
FUNCTION sfgetlotexpirydate (
p_lot_number mtl_lot_numbers.lot_number%TYPE
) RETURN DATE
RESULT_CACHE;
FUNCTION sfgetitemunitpirce (
p_item_id mtl_system_items_b.inventory_item_id%TYPE,
p_organization_id mtl_system_items_b.organization_id%TYPE
) RETURN NUMBER
RESULT_CACHE;
FUNCTION sfgetwarehousename (
p_organization_id org_organization_definitions.organization_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetoperatingunitname (
p_organization_id org_organization_definitions.organization_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetcustomeritem (
p_customer_id oe_order_headers_all.sold_to_org_id%TYPE,
p_item oe_order_lines_all.ordered_item%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetonhandqty (
p_organization_id mtl_onhand_total_mwb_v.organization_id%TYPE,
p_subinventory_code mtl_onhand_total_mwb_v.subinventory_code%TYPE,
p_item_id mtl_onhand_total_mwb_v.inventory_item_id%TYPE
) RETURN NUMBER
RESULT_CACHE;
FUNCTION sfgettransactionparty (
p_transaction_type_name mtl_transaction_types.transaction_type_name%TYPE,
p_transactionreference VARCHAR2
) RETURN VARCHAR2;
FUNCTION sfgettransactionreference (
p_transaction_type_name mtl_transaction_types.transaction_type_name%TYPE,
p_transactionreference VARCHAR2,
p_flag VARCHAR2 DEFAULT ‘@@@’
) RETURN VARCHAR2;
FUNCTION sfgetlotavailable (
p_organization_id mtl_onhand_total_mwb_v.organization_id%TYPE,
p_subinventory_code mtl_onhand_total_mwb_v.subinventory_code%TYPE,
p_item_id mtl_onhand_total_mwb_v.inventory_item_id%TYPE,
p_lot_number mtl_transaction_lot_numbers.lot_number%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetsoqty (
p_item_id mtl_system_items_b.inventory_item_id%TYPE,
p_cust_account_id hz_cust_accounts.cust_account_id%TYPE
) RETURN NUMBER
RESULT_CACHE;
FUNCTION sfgetsubinvcode (
p_item_id mtl_system_items_b.inventory_item_id%TYPE,
p_organization_id mtl_system_items_b.organization_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetpartyname (
p_cust_accounts hz_cust_accounts.cust_account_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetinvoicenobyordernum (
p_order_num oe_order_headers_all.order_number%TYPE,
p_flag VARCHAR2
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION uomconversionrate (
p_inventory_item_id mtl_uom_conversions.inventory_item_id%TYPE,
p_uom_code VARCHAR2
) RETURN NUMBER
RESULT_CACHE;
FUNCTION uomqty (
p_uom_code VARCHAR2
) RETURN NUMBER
RESULT_CACHE;
FUNCTION uomclassconversion_to_pcs (
p_from_uom_code VARCHAR2,
p_base_uom_code VARCHAR2,
— p_to_uom_code VARCHAR2,
p_inventory_item_id NUMBER,
p_ordered_quantity NUMBER
) RETURN NUMBER
RESULT_CACHE;
FUNCTION uomclassconversion (
p_from_uom_code VARCHAR2,
p_to_uom_code VARCHAR2,
p_inventory_item_id NUMBER
) RETURN NUMBER;
FUNCTION sfgetcustcode (
p_cust_account_id hz_cust_accounts.cust_account_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetoperating_ut (
p_organization_id org_organization_definitions.organization_id%TYPE
) RETURN NUMBER
RESULT_CACHE;
FUNCTION sfgettrxdate (
p_trx_number ra_customer_trx_all.trx_number%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sflookupvalidate (
p_lookupcode xxa_DOY_apex_user_lookups.lookup_code%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetsoopenqty (
p_inventory_item_id oe_order_lines_all.inventory_item_id%TYPE,
p_organization_id mtl_system_items_b.organization_id%TYPE
) RETURN NUMBER;
FUNCTION sfgetpoopenqty (
p_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE,
p_organization_id mtl_system_items_b.organization_id%TYPE
) RETURN NUMBER;
FUNCTION sfgetorgcode (
p_organization_id org_organization_definitions.organization_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetcurrate (
p_trx_number ra_customer_trx_all.trx_number%TYPE,
p_type VARCHAR2
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetexchangerate (
p_trx_number ra_customer_trx_all.trx_number%TYPE
) RETURN NUMBER
RESULT_CACHE;
FUNCTION fntranactualcost (
p_line_id oe_order_lines_all.line_id%TYPE
) RETURN NUMBER
RESULT_CACHE;
FUNCTION fnaradjustments (
p_cust_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
p_type ar_adjustments_all.type%TYPE
) RETURN NUMBER;
FUNCTION fnglsegment (
p_code_combination_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION fntrxtype (
p_customer_trx_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION fnrasource (
p_batch_source_id NUMBER,
p_org_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION fntrxclass (
p_customer_trx_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION fnfindbaseuomconversion (
p_primary_uom_code VARCHAR2
) RETURN NUMBER
RESULT_CACHE;
FUNCTION fnfdsameclass_con_rate (
p_inventory_item_id NUMBER,
p_to_uom_code VARCHAR2
) RETURN NUMBER
RESULT_CACHE;
FUNCTION fnitemsourcemanufacture (
p_inventory_item_id NUMBER,
p_org_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION fnpoquantity (
p_inventory_item_id NUMBER,
p_org_id NUMBER
) RETURN NUMBER;
FUNCTION fnunitcostondate (
p_organization_id NUMBER,
p_subinventory_code VARCHAR2,
p_locator_id NUMBER,
p_inventory_item_id NUMBER,
p_start_date DATE,
p_end_date DATE
) RETURN NUMBER;
FUNCTION fnunitcoston_enddate (
p_organization_id NUMBER,
p_subinventory_code VARCHAR2,
p_locator_id NUMBER,
p_inventory_item_id NUMBER,
p_end_date DATE
) RETURN NUMBER;
FUNCTION fnsohhistory (
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_subinventory_code VARCHAR2,
p_start_date DATE
) RETURN NUMBER
RESULT_CACHE;
FUNCTION fnnumber (
p_number NUMBER
) RETURN VARCHAR2;
FUNCTION fnprepaymentapplied (
p_invoice NUMBER
) RETURN NUMBER
RESULT_CACHE;
FUNCTION fnamountapplied (
p_invoice_id NUMBER
) RETURN NUMBER
RESULT_CACHE;
FUNCTION fn_gl_batch (
p_invoice_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION fn_gl_transfer (
p_invoice_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION fn_gl_transfer_status (
p_invoice_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION fnenddatesohhis (
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_subinventory_code VARCHAR2,
p_start_date DATE,
p_end_date DATE
) RETURN NUMBER;
FUNCTION fnsohsub (
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_subinventory_code VARCHAR2,
p_start_date DATE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION fn_mfg_lookups (
p_lookup_type VARCHAR2,
p_lookup_code VARCHAR2
) RETURN VARCHAR2;
FUNCTION fn_categories (
p_inventory_item_id NUMBER,
p_organization_id NUMBER,
p_category_set_name VARCHAR2
) RETURN VARCHAR2;
FUNCTION fncustxresfsinfo (
p_inventory_item_id NUMBER,
p_type VARCHAR2
) RETURN VARCHAR2;
FUNCTION fn_variance_amt (
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_from_date VARCHAR2,
p_to_date VARCHAR2
) RETURN NUMBER
RESULT_CACHE;
FUNCTION fn_creditno (
p_customer_trx_id NUMBER,
p_type VARCHAR2
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION fnarpost (
p_trx_number VARCHAR2,
p_cust_trx_line_gl_dist_id NUMBER,
p_code_combination_id NUMBER,
p_event_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION fnreceiptpoquantity (
po_number NUMBER,
p_inventory_item_id NUMBER,
p_org_id NUMBER,
p_type VARCHAR2
) RETURN NUMBER;
FUNCTION fnvariancebycustomer (
p_organization_id NUMBER,
p_sold_to_org_id NUMBER,
p_from_date VARCHAR2,
p_to_date VARCHAR2,
p_type varchar2 default null
) RETURN NUMBER RESULT_CACHE;
END xxa_pkgutil;
/
create or replace PACKAGE BODY xxa_pkgutil AS
—
/**********************************************************************************************
* OBJECT TYPE : Package Body *
* OBJECT NAME : xxa_pkgutil *
* PURPOSE : This is a utility package to access minor information *
* *
* Author Version Date Description *
* ————- ——- ———— —————————————————— *
* Jegan 1.0 16-DEC-2022 Intial version ** *
***********************************************************************************************/
————————————————————
/*To get colon separated value */
————————————————————
FUNCTION sflist (
p_str VARCHAR2,
p_separator VARCHAR2
) RETURN ty_chartbllist AS
–return number as
l_list ty_chartbllist;
l_cursor SYS_REFCURSOR;
l_str VARCHAR2(4000);
lindex NUMBER;
BEGIN
IF l_cursor%isopen THEN
CLOSE l_cursor;
END IF;
l_str := ‘select regexp_substr (‘
|| chr(39)
|| p_str
|| chr(39)
|| ‘,’
|| chr(39)
|| ‘[^’
|| p_separator
|| ‘]+’
|| chr(39)
|| ‘,1,level) discount FROM DUAL connect by level <= length (‘
|| chr(39)
|| p_str
|| chr(39)
|| ‘ ) – length ( replace ( ‘
|| chr(39)
|| p_str
|| chr(39)
|| ‘, ‘
|| chr(39)
|| p_separator
|| chr(39)
|| ‘ ) ) + 1’;
OPEN l_cursor FOR l_str;
FETCH l_cursor
BULK COLLECT INTO l_list;
CLOSE l_cursor;
RETURN l_list;
END sflist;
————————————————————
/*To get PO remaining Qty */
————————————————————
FUNCTION sfgetporemainingqty (
p_item_id po_lines_all.item_id%TYPE,
p_ship_to_location_id po_line_locations_all.ship_to_location_id%TYPE
) RETURN NUMBER
RESULT_CACHE
AS
lv_result NUMBER;
BEGIN
SELECT
SUM(nvl(b.quantity, 0) – nvl(b.quantity_received, 0))
INTO lv_result
FROM
po_lines_all a,
po_line_locations_all b
WHERE
a.po_line_id = b.po_line_id
AND a.item_id = p_item_id
AND b.ship_to_location_id = p_ship_to_location_id;
RETURN nvl(lv_result, 0);
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END sfgetporemainingqty;
————————————————————
/*To get LOT Stock Date */
————————————————————
FUNCTION sfgetlotcreationdate (
p_lot_number mtl_lot_numbers.lot_number%TYPE
) RETURN DATE
RESULT_CACHE
AS
lv_result DATE;
BEGIN
SELECT
nvl(to_date(attribute5, ‘DD-Mon-RR’), creation_date)
INTO lv_result
FROM
mtl_lot_numbers mtlv
WHERE
1 = 1
AND mtlv.lot_number = p_lot_number;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgetlotcreationdate;
————————————————————
/*To get LOT DFF Date */
————————————————————
FUNCTION sfgetlotdffdate (
p_lot_number mtl_lot_numbers.lot_number%TYPE
) RETURN DATE
RESULT_CACHE
AS
lv_result DATE;
BEGIN
SELECT
to_date(attribute5, ‘DD-Mon-RR’)
INTO lv_result
FROM
mtl_transaction_lot_numbers–,mtl_onhand_quantities ,EXPIRATION_DATE
WHERE
lot_number = p_lot_number;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgetlotdffdate;
————————————————————
/*To get LOT expiry Date */
————————————————————
FUNCTION sfgetlotexpirydate (
p_lot_number mtl_lot_numbers.lot_number%TYPE
) RETURN DATE
RESULT_CACHE
AS
lv_result DATE;
BEGIN
SELECT
expiration_date
INTO lv_result
FROM
mtl_lot_numbers–,mtl_onhand_quantities ,EXPIRATION_DATE
WHERE
lot_number = p_lot_number;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgetlotexpirydate;
————————————————————
/*To get Item Unit price */
————————————————————
FUNCTION sfgetitemunitpirce (
p_item_id mtl_system_items_b.inventory_item_id%TYPE,
p_organization_id mtl_system_items_b.organization_id%TYPE
) RETURN NUMBER
RESULT_CACHE
AS
lv_result NUMBER;
BEGIN
SELECT
— round(item_cost, 5)
SUM(item_cost)
INTO lv_result
FROM
cst_item_cost_type_v
WHERE
organization_id = p_organization_id
AND inventory_item_id = p_item_id;
–AND cost_type = ‘BUD’;– Frozen,Pending
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END sfgetitemunitpirce;
————————————————————
/*To get warehouse name */
————————————————————
FUNCTION sfgetwarehousename (
p_organization_id org_organization_definitions.organization_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE
AS
lv_result VARCHAR2(50);
BEGIN
SELECT
organization_name d
INTO lv_result
FROM
org_organization_definitions
WHERE
organization_id = p_organization_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN p_organization_id;
END sfgetwarehousename;
————————————————————
/*To get Operating Unit name */
————————————————————
FUNCTION sfgetoperatingunitname (
p_organization_id org_organization_definitions.organization_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE
AS
lv_result VARCHAR2(50);
BEGIN
SELECT
name
INTO lv_result
FROM
hr_operating_units
WHERE
organization_id = p_organization_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN p_organization_id;
END sfgetoperatingunitname;

————————————————————
/*To get Customer item name */
————————————————————
FUNCTION sfgetcustomeritem (
p_customer_id oe_order_headers_all.sold_to_org_id%TYPE,
p_item oe_order_lines_all.ordered_item%TYPE
) RETURN VARCHAR2
RESULT_CACHE
AS
lv_result VARCHAR2(50);
BEGIN
SELECT DISTINCT
MAX(a.customer_item_number)
INTO lv_result
FROM
mtl_customer_items_all_v a,
mtl_customer_item_xrefs_v b/*,
(
SELECT
header_id,
order_number,
sold_to_org_id
FROM
oe_order_headers_all
) c,
(
SELECT
header_id,
line_id,
ordered_item
FROM
oe_order_lines_all
) d */
WHERE
a.customer_item_id = b.customer_item_id (+)
AND a.customer_status = ‘A’
AND a.inactive_flag = ‘N’
AND b.inactive_flag = ‘N’
AND a.customer_id = p_customer_id
AND b.concatenated_segments = p_item
/* AND a.customer_id = c.sold_to_org_id
AND c.header_id = d.header_id
AND b.concatenated_segments = d.ordered_item
AND c.header_id = p_header_id
AND d.line_id = p_line_id –OOLA.line_id */;

RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgetcustomeritem;
————————————————————
/*To get OnHand Qty */
————————————————————
FUNCTION sfgetonhandqty (
p_organization_id mtl_onhand_total_mwb_v.organization_id%TYPE,
p_subinventory_code mtl_onhand_total_mwb_v.subinventory_code%TYPE,
p_item_id mtl_onhand_total_mwb_v.inventory_item_id%TYPE
) RETURN NUMBER
RESULT_CACHE
AS
v_retvalue NUMBER;
BEGIN
SELECT
SUM(on_hand.on_hand) on_hand_qty
INTO v_retvalue
FROM
mtl_onhand_total_mwb_v on_hand
WHERE
on_hand.organization_id = nvl(p_organization_id, on_hand.organization_id) –ORG ID
AND on_hand.inventory_item_id = p_item_id
AND on_hand.subinventory_code = nvl(p_subinventory_code, on_hand.subinventory_code) –sub inventory
;
RETURN nvl(v_retvalue, 0);
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END sfgetonhandqty;
————————————————————
/*To get OnHand Qty */
————————————————————
FUNCTION sfgettransactionparty (
p_transaction_type_name mtl_transaction_types.transaction_type_name%TYPE,
p_transactionreference VARCHAR2
) RETURN VARCHAR2 AS
v_retvalue VARCHAR2(50);
BEGIN
IF upper(p_transaction_type_name) = upper(‘Sales order issue’) THEN
SELECT
hzc.account_number
INTO v_retvalue
FROM
oe_order_headers_all oh,
oe_order_lines_all ol,
hz_cust_accounts hzc
WHERE
oh.header_id = ol.header_id
AND oh.sold_to_org_id = hzc.cust_account_id
AND ol.line_id = p_transactionreference;
ELSIF upper(p_transaction_type_name) = upper(‘PO Receipt’) THEN
SELECT
ven.segment1
INTO v_retvalue
FROM
po_headers_all h,
po_lines_all l,
rcv_transactions rcv,
po_vendors ven
WHERE
h.po_header_id = l.po_header_id
AND rcv.po_line_id = l.po_line_id
AND ven.vendor_id = h.vendor_id
AND rcv.transaction_id = p_transactionreference;
— ELSIF upper(p_transaction_type_name) = upper(‘PO Receipt’) THEN
END IF;
RETURN v_retvalue;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgettransactionparty;
————————————————————
/*To get OnHand Qty */
————————————————————
FUNCTION sfgettransactionreference (
p_transaction_type_name mtl_transaction_types.transaction_type_name%TYPE,
p_transactionreference VARCHAR2,
p_flag VARCHAR2 DEFAULT ‘@@@’
) RETURN VARCHAR2 AS
v_retvalue VARCHAR2(50);
BEGIN
IF upper(p_transaction_type_name) = upper(‘Sales order issue’) THEN
SELECT
hzc.account_number
INTO v_retvalue
FROM
oe_order_headers_all oh,
oe_order_lines_all ol,
hz_cust_accounts hzc
WHERE
oh.header_id = ol.header_id
AND oh.sold_to_org_id = hzc.cust_account_id
AND ol.line_id = p_transactionreference;
ELSIF upper(p_transaction_type_name) = upper(‘PO Receipt’) THEN
SELECT
receipt_num
INTO v_retvalue
FROM
rcv_transactions a,
rcv_shipment_headers b
WHERE
a.transaction_id = p_transactionreference
AND b.shipment_header_id = a.shipment_header_id;

— ELSIF upper(p_transaction_type_name) = upper(‘PO Receipt’) THEN

END IF;
RETURN v_retvalue;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgettransactionreference;
————————————————————
/*To get Lot Availabilty */
————————————————————
FUNCTION sfgetlotavailable (
p_organization_id mtl_onhand_total_mwb_v.organization_id%TYPE,
p_subinventory_code mtl_onhand_total_mwb_v.subinventory_code%TYPE,
p_item_id mtl_onhand_total_mwb_v.inventory_item_id%TYPE,
p_lot_number mtl_transaction_lot_numbers.lot_number%TYPE
) RETURN VARCHAR2
RESULT_CACHE
AS
v_retvalue VARCHAR2(54);
BEGIN
SELECT
‘Available’
INTO v_retvalue
FROM
mtl_onhand_total_mwb_v on_hand
WHERE
on_hand.organization_id = nvl(p_organization_id, on_hand.organization_id) –ORG ID
AND on_hand.inventory_item_id = p_item_id
AND on_hand.subinventory_code = p_subinventory_code –sub inventory
AND on_hand.lot_number = p_lot_number;
RETURN nvl(v_retvalue, ‘Not Available’);
EXCEPTION
WHEN OTHERS THEN
RETURN ‘Not Available’;
END sfgetlotavailable;

————————————————————
/*To get SO Qty */
————————————————————
FUNCTION sfgetsoqty (
p_item_id mtl_system_items_b.inventory_item_id%TYPE,
p_cust_account_id hz_cust_accounts.cust_account_id%TYPE
) RETURN NUMBER
RESULT_CACHE
AS
lv_result NUMBER;
BEGIN
SELECT
SUM(nvl(ol.ordered_quantity, 0) – nvl(ol.shipped_quantity, 0))
INTO lv_result
FROM
oe_order_headers_all oh,
oe_order_lines_all ol
WHERE
oh.header_id = ol.header_id
AND oh.sold_to_org_id = p_cust_account_id
— hzc.cust_account_id
AND ol.inventory_item_id = p_item_id;
— AND b.ship_to_location_id = p_ship_to_location_id;

RETURN nvl(lv_result, 0);
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END sfgetsoqty;

————————————————————
/*To get Sub invemtory Code */
————————————————————
FUNCTION sfgetsubinvcode (
p_item_id mtl_system_items_b.inventory_item_id%TYPE,
p_organization_id mtl_system_items_b.organization_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE
AS
lv_result VARCHAR2(30);
BEGIN
SELECT DISTINCT
moqd.subinventory_code
INTO lv_result
FROM
mtl_onhand_quantities moqd –mtl_onhand_quantities_detail
WHERE
1 = 1
AND moqd.inventory_item_id = p_item_id
AND moqd.organization_id = p_organization_id;

RETURN nvl(lv_result, NULL);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgetsubinvcode;
————————————————————
/*To get Party name */
————————————————————
FUNCTION sfgetpartyname (
p_cust_accounts hz_cust_accounts.cust_account_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE
AS
lv_result VARCHAR2(100);
BEGIN
SELECT
hzp.party_name
INTO lv_result
FROM
apps.hz_parties hzp,
hz_cust_accounts hca
WHERE
hzp.party_id = hca.party_id
AND hca.cust_account_id = p_cust_accounts;
RETURN nvl(lv_result, p_cust_accounts);
EXCEPTION
WHEN OTHERS THEN
RETURN p_cust_accounts;
END sfgetpartyname;
————————————————————
/*To get invoice number and date by order number */
————————————————————
FUNCTION sfgetinvoicenobyordernum (
p_order_num oe_order_headers_all.order_number%TYPE,
p_flag VARCHAR2
) RETURN VARCHAR2
RESULT_CACHE
AS
lv_result VARCHAR2(100);
BEGIN
IF upper(p_flag) = ‘INVOICE’ THEN
SELECT DISTINCT
rct.trx_number
–rct.TRX_date Invoice_Date,
INTO lv_result
FROM
apps.ra_customer_trx_lines_all rctl,
apps.ra_customer_trx_all rct
–,apps.mtl_system_items msi
WHERE
rct.customer_trx_id = rctl.customer_trx_id
AND rctl.line_type = ‘LINE’
AND rctl.interface_line_attribute1 = to_char(p_order_num);
ELSIF upper(p_flag) = ‘DATE’ THEN
SELECT DISTINCT
rct.trx_date
–rct.TRX_date Invoice_Date,
INTO lv_result
FROM
apps.ra_customer_trx_lines_all rctl,
apps.ra_customer_trx_all rct
–,apps.mtl_system_items msi
WHERE
rct.customer_trx_id = rctl.customer_trx_id
AND rctl.line_type = ‘LINE’
AND rctl.interface_line_attribute1 = to_char(p_order_num);
END IF;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgetinvoicenobyordernum;
————————————————————
/*To get uom conversion rate */
————————————————————
FUNCTION uomconversionrate (
p_inventory_item_id mtl_uom_conversions.inventory_item_id%TYPE,
p_uom_code VARCHAR2
) RETURN NUMBER
RESULT_CACHE
AS
lv_result NUMBER;
BEGIN
SELECT
conversion_rate
INTO lv_result
FROM
mtl_uom_conversions
WHERE
inventory_item_id = p_inventory_item_id
AND uom_code = p_uom_code;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END uomconversionrate;
————————————————————

————————————————————
FUNCTION uomqty (
p_uom_code VARCHAR2
) RETURN NUMBER
RESULT_CACHE
AS
lv_result NUMBER;
BEGIN
SELECT
conversion_rate
INTO lv_result
FROM
mtl_uom_conversions
WHERE
inventory_item_id = 0
AND uom_code = p_uom_code;

RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END uomqty;
————————————————————
/*To convert conversion rate to PCS */
————————————————————
FUNCTION uomclassconversion_to_pcs (
p_from_uom_code VARCHAR2,
p_base_uom_code VARCHAR2,
— p_to_uom_code VARCHAR2,
p_inventory_item_id NUMBER,
p_ordered_quantity NUMBER
) RETURN NUMBER
RESULT_CACHE
IS
lv_return NUMBER;
BEGIN
lv_return :=
CASE
WHEN p_from_uom_code IN ( ‘DOZ’, ‘PLT’, ‘BN’, ‘FT’, ‘GR’,
‘Rol’, ‘SET’, ‘SLV’, ‘K’, ‘TH’ ) THEN
( p_ordered_quantity / xxa_pkgutil.uomqty(p_from_uom_code) ) –* xxa_pkgutil.uomqty(p_to_uom_code)
WHEN p_from_uom_code IN ( ‘CT’ ) THEN
( p_ordered_quantity / xxa_pkgutil.uomconversionrate(p_inventory_item_id, p_from_uom_code) )
— WHEN p_from_uom_code IN ( ‘TH’ ) THEN
— ( p_ordered_quantity / xxa_pkgutil.uomconversionrate(p_inventory_item_id,p_from_uom_code) )
WHEN p_from_uom_code = ‘Ea’ THEN
( p_ordered_quantity / ( uomclassconversion(p_from_uom_code, p_base_uom_code, p_inventory_item_id) ) ) –* xxa_pkgutil.uomqty(p_to_uom_code)
WHEN p_from_uom_code = ‘KM’ THEN
( p_ordered_quantity / ( uomclassconversion(p_from_uom_code, p_base_uom_code, p_inventory_item_id) ) ) –* xxa_pkgutil.uomqty(p_to_uom_code)
WHEN p_from_uom_code = ‘M’ THEN
( p_ordered_quantity / ( uomclassconversion(p_from_uom_code, p_base_uom_code, p_inventory_item_id) ) ) –* xxa_pkgutil.uomqty(p_to_uom_code)
WHEN p_from_uom_code = ‘KG’ THEN
( p_ordered_quantity / ( uomclassconversion(p_from_uom_code, p_base_uom_code, p_inventory_item_id) ) ) –* xxa_pkgutil.uomqty(p_to_uom_code)
END;
RETURN lv_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END uomclassconversion_to_pcs;
————————————————————
/*To convert UOM from 1 class to another class */
————————————————————
FUNCTION uomclassconversion (
p_from_uom_code VARCHAR2,
p_to_uom_code VARCHAR2,
p_inventory_item_id NUMBER
) RETURN NUMBER IS
lv_result NUMBER;
BEGIN
SELECT
conversion_rate
INTO lv_result
FROM
mtl_uom_class_conversions uom
WHERE
uom.from_uom_code = p_from_uom_code
AND uom.to_uom_code = p_to_uom_code
AND uom.inventory_item_id = p_inventory_item_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END uomclassconversion;
————————————————————
/*To get customer code */
————————————————————
FUNCTION sfgetcustcode (
p_cust_account_id hz_cust_accounts.cust_account_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE
AS
lv_result VARCHAR2(100);
BEGIN
SELECT
hca.account_number
INTO lv_result
FROM
apps.hz_parties hzp,
hz_cust_accounts hca
WHERE
hzp.party_id = hca.party_id
AND hca.cust_account_id = p_cust_account_id;
RETURN nvl(lv_result, p_cust_account_id);
EXCEPTION
WHEN OTHERS THEN
RETURN p_cust_account_id;
END sfgetcustcode;
————————————————————
/*To get Operating Unit */
————————————————————
FUNCTION sfgetoperating_ut (
p_organization_id org_organization_definitions.organization_id%TYPE
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT
operating_unit
INTO lv_result
FROM
org_organization_definitions
WHERE
organization_id = p_organization_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN p_organization_id;
END sfgetoperating_ut;
————————————————————
/*To get Trx Date */
————————————————————
FUNCTION sfgettrxdate (
p_trx_number ra_customer_trx_all.trx_number%TYPE
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(30);
BEGIN
SELECT
trx_date
INTO lv_result
FROM
ra_customer_trx_all
WHERE
trx_number = p_trx_number;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgettrxdate;
————————————————————
/*To get Lookup validity */
————————————————————
FUNCTION sflookupvalidate (
p_lookupcode xxa_doy_apex_user_lookups.lookup_code%TYPE
) RETURN VARCHAR2
RESULT_CACHE
AS
lv_result VARCHAR2(50);
BEGIN
SELECT
‘Y’
INTO lv_result
FROM
xxa_doy_apex_user_lookups
WHERE
lookup_type = ‘USER_ROLE’
AND nvl(active_flag, ‘Y’) = ‘Y’
AND sysdate BETWEEN nvl(start_date, sysdate) AND nvl(end_date, sysdate)
AND lookup_code = p_lookupcode;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN ‘N’;
END sflookupvalidate;
————————————————————
/*To get So Open QTY */
————————————————————
FUNCTION sfgetsoopenqty (
p_inventory_item_id oe_order_lines_all.inventory_item_id%TYPE,
p_organization_id mtl_system_items_b.organization_id%TYPE
) RETURN NUMBER AS
lv_result NUMBER;
BEGIN
SELECT
SUM(nvl(oola.ordered_quantity, 0) – nvl(oola.shipped_quantity, 0) – nvl(oola.cancelled_quantity, 0))
INTO lv_result
FROM
apps.oe_order_headers_all ooha,
apps.oe_order_lines_all oola
WHERE
1 = 1
AND ooha.header_id = oola.header_id
— AND ooha.org_id = oola.org_id
AND ooha.ship_from_org_id = p_organization_id
AND oola.inventory_item_id = p_inventory_item_id
AND oola.open_flag = ‘Y’
GROUP BY
oola.inventory_item_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END sfgetsoopenqty;
————————————————————
/*To get PO Open QTY */
————————————————————
FUNCTION sfgetpoopenqty (
p_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE,
p_organization_id mtl_system_items_b.organization_id%TYPE
) RETURN NUMBER AS
lv_result NUMBER;
BEGIN
SELECT
SUM(quantity)
INTO lv_result
FROM
po_headers_all ph,
po_lines_all pl,
po_distributions_all pda
WHERE
1 = 1
AND ph.po_header_id = pl.po_header_id
AND pl.item_id = p_inventory_item_id– ‘1172212’
AND ph.cancel_flag <> ‘Y’
AND pda.po_header_id = ph.po_header_id
AND pda.po_line_id = pl.po_line_id
AND pda.destination_organization_id = p_organization_id
AND ph.closed_code = ‘OPEN’
GROUP BY
pl.item_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END sfgetpoopenqty;
————————————————————
/*To get Org Code */
————————————————————
FUNCTION sfgetorgcode (
p_organization_id org_organization_definitions.organization_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(30);
BEGIN
SELECT
o.organization_code
INTO lv_result
— , o.operating_unit
FROM
org_organization_definitions o,
hr_operating_units hr
WHERE
o.operating_unit = hr.organization_id
AND o.organization_id = p_organization_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgetorgcode;
————————————————————
/*To get Currency type and exchange rate */
————————————————————
FUNCTION sfgetcurrate (
p_trx_number ra_customer_trx_all.trx_number%TYPE,
p_type VARCHAR2
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(30);
BEGIN
IF p_type = ‘CUR’ THEN
SELECT DISTINCT
invoice_currency_code
INTO lv_result
— , o.operating_unit
FROM
ra_customer_trx_all
WHERE
trx_number = p_trx_number
AND invoice_currency_code IS NOT NULL;
ELSIF p_type = ‘RATE’ THEN
SELECT DISTINCT
exchange_rate
INTO lv_result
— , o.operating_unit
FROM
ra_customer_trx_all
WHERE
trx_number = p_trx_number
AND exchange_rate IS NOT NULL;
END IF;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgetcurrate;
————————————————————
/*To get exchange rate */
————————————————————
FUNCTION sfgetexchangerate (
p_trx_number ra_customer_trx_all.trx_number%TYPE
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT DISTINCT
( exchange_rate )
INTO lv_result
— , o.operating_unit
FROM
ra_customer_trx_all
WHERE
trx_number = p_trx_number
AND exchange_rate IS NOT NULL;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgetexchangerate;
————————————————————
/*To get Transaction actual cost */
————————————————————
FUNCTION fntranactualcost (
p_line_id oe_order_lines_all.line_id%TYPE
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT DISTINCT
mmt.actual_cost
INTO lv_result
FROM
wsh_delivery_details wdd
LEFT JOIN mtl_material_transactions mmt ON ( wdd.delivery_detail_id = mmt.picking_line_id )
LEFT JOIN mtl_transaction_accounts mta ON ( mmt.transaction_id = mta.transaction_id )
WHERE
wdd.source_line_id = p_line_id
AND mta.primary_quantity > 0;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fntranactualcost;
————————————————————
/*To get Adjustmnets Amount */
————————————————————
FUNCTION fnaradjustments (
p_cust_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
p_type ar_adjustments_all.type%TYPE
) RETURN NUMBER IS
lv_result NUMBER;
BEGIN
SELECT
decode(p_type, ‘TAX’, tax_adjusted, ‘LINE’, line_adjusted)
INTO lv_result
FROM
ar_adjustments_all
WHERE
customer_trx_id = p_cust_trx_id
AND type = p_type;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fnaradjustments;
————————————————————
/*To get Gl Segment */
————————————————————
FUNCTION fnglsegment (
p_code_combination_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(150);
BEGIN
SELECT
concatenated_segments
INTO lv_result
FROM
gl_code_combinations_kfv
WHERE
code_combination_id = p_code_combination_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fnglsegment;
————————————————————
/*To get transaction type */
————————————————————
FUNCTION fntrxtype (
p_customer_trx_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(150);
BEGIN
SELECT
name
INTO lv_result
FROM
ra_cust_trx_types_all
WHERE
cust_trx_type_id = p_customer_trx_id; — ( 12026, 20029,12027,20027 )
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fntrxtype;
————————————————————
/*To get transaction type */
————————————————————
FUNCTION fnrasource (
p_batch_source_id NUMBER,
p_org_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(150);
BEGIN
SELECT
name
INTO lv_result
FROM
ra_batch_sources_all a
WHERE
1 = 1
AND a.batch_source_id = p_batch_source_id
AND a.org_id = p_org_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fnrasource;
————————————————————
/*To get transaction Class */
————————————————————
FUNCTION fntrxclass (
p_customer_trx_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(150);
BEGIN
SELECT DISTINCT
meaning
INTO lv_result
FROM
ra_cust_trx_types_all types,
ar_lookups l1
WHERE
types.type = l1.lookup_code
AND cust_trx_type_id = p_customer_trx_id
AND enabled_flag = ‘Y’
AND lookup_type = ‘INV/CM/ADJ’
AND sysdate BETWEEN start_date_active AND nvl(end_date_active, sysdate);
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fntrxclass;
————————————————————
/*Find Base Uom Conversion */
————————————————————
FUNCTION fnfindbaseuomconversion (
p_primary_uom_code VARCHAR2
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT
muc.conversion_rate
INTO lv_result
FROM
–MTL_SYSTEM_ITEMS_B MSIB,
mtl_uom_conversions muc,
mtl_units_of_measure mum
WHERE
1 = 1
AND muc.uom_code = p_primary_uom_code
AND muc.inventory_item_id = 0
AND muc.uom_class = mum.uom_class
AND base_uom_flag = ‘Y’;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fnfindbaseuomconversion;
————————————————————
/*Find same class Uom Conversion rate */
————————————————————
FUNCTION fnfdsameclass_con_rate (
— p_primary_uom_code VARCHAR2,
p_inventory_item_id NUMBER,
p_to_uom_code VARCHAR2
— p_organization_id number
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT
mucc.conversion_rate
INTO lv_result
FROM
mtl_uom_class_conversions mucc
— MTL_SYSTEM_ITEMS_B MSIB
WHERE
mucc.inventory_item_id = p_inventory_item_id
AND to_uom_code = p_to_uom_code;
— and MSIB.segment1 IN ( ‘20081620’, ‘10508930’ )
— AND MSIB.organization_id = 329
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fnfdsameclass_con_rate;
————————————————————
/*To get item manufacture source */
————————————————————
FUNCTION fnitemsourcemanufacture (
p_inventory_item_id NUMBER,
p_org_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(150);
BEGIN
SELECT
fl.description
INTO lv_result
FROM
mtl_item_categories mic,
mtl_categories_b mcb,
mtl_category_sets_tl mcst,
apps.fnd_flex_values_vl fl
WHERE
mic.inventory_item_id = p_inventory_item_id
AND mic.category_id (+) = mcb.category_id
AND mic.category_set_id = mcst.category_set_id
AND fl.flex_value = mcb.segment5
AND fl.flex_value_meaning = mcb.segment5
AND mcst.category_set_name = ‘Inventory’
AND mcb.segment1 = ‘Flex_FG’
AND mic.organization_id = p_org_id
AND fl.description IS NOT NULL;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fnitemsourcemanufacture;
FUNCTION fnpoquantity (
p_inventory_item_id NUMBER,
p_org_id NUMBER
) RETURN NUMBER IS
lv_result NUMBER;
BEGIN
SELECT
SUM(quantity)
INTO lv_result
FROM
(
SELECT
( SUM(nvl(plla.quantity, 0)) – SUM(nvl(plla.quantity_received, 0)) – SUM(nvl(plla.quantity_cancelled, 0)) – SUM(nvl(
plla.quantity_rejected, 0)) ) quantity
FROM
po_headers_all ph,
po_lines_all pl,
hr_locations_all hla,
po_line_locations_all plla,
po_releases_all pra
WHERE
1 = 1
AND ph.po_header_id = pl.po_header_id
AND pl.item_id = p_inventory_item_id
AND hla.location_id = ph.ship_to_location_id
AND pl.po_line_id = plla.po_line_id
AND pra.po_release_id = plla.po_release_id
AND nvl(pl.closed_code, ‘OPEN’) = ‘OPEN’
AND nvl(pra.closed_code, ‘OPEN’) = ‘OPEN’
AND type_lookup_code = ‘BLANKET’
AND inventory_organization_id = p_org_id
UNION
SELECT
( SUM(nvl(plla.quantity, 0)) – SUM(nvl(plla.quantity_received, 0)) – SUM(nvl(plla.quantity_cancelled, 0)) – SUM(nvl(
plla.quantity_rejected, 0)) ) quantity
FROM
po_headers_all ph
LEFT JOIN po_lines_all pl ON ph.po_header_id = pl.po_header_id
LEFT JOIN hr_locations_all hla ON hla.location_id = ph.ship_to_location_id
LEFT JOIN po_line_locations_all plla ON pl.po_line_id = plla.po_line_id
WHERE
1 = 1
AND pl.item_id = p_inventory_item_id—1395773
AND nvl(pl.closed_code, ‘OPEN’) = ‘OPEN’
AND ph.closed_code = ‘OPEN’
AND type_lookup_code = ‘STANDARD’
AND inventory_organization_id = p_org_id —1395926
);
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fnpoquantity;
————————————————————————————-
–To find Unit cost on specific date.
————————————————————————————-
FUNCTION fnunitcostondate (
p_organization_id NUMBER,
p_subinventory_code VARCHAR2,
p_locator_id NUMBER,
p_inventory_item_id NUMBER,
p_start_date DATE,
p_end_date DATE
) RETURN NUMBER IS
lv_result NUMBER;
BEGIN
SELECT
actual_cost
INTO lv_result
FROM
(
SELECT
mcd.new_cost actual_cost
FROM
mtl_cst_actual_cost_details mcd,
apps.mtl_material_transactions mtr,
mtl_transaction_types mtt
WHERE
1 = 1
AND mcd.transaction_id = mtr.transaction_id
AND mtr.organization_id = p_organization_id
AND mcd.inventory_item_id = mtr.inventory_item_id
AND mcd.organization_id = mtr.organization_id
— AND mtr.subinventory_code = nvl(p_subinventory_code,mtr.subinventory_code)
AND mtr.inventory_item_id = p_inventory_item_id
AND mtr.transaction_type_id = mtt.transaction_type_id
— AND MTT.transaction_type_name IN (‘Sales Order Pick’,’Sales order issue’,’PO Receipt’,’Legacy Opening Stock’)
AND trunc(mtr.transaction_date) < to_char(p_start_date, ‘DD-MON-YYYY’)
AND mcd.new_cost IS NOT NULL
ORDER BY
mcd.transaction_costed_date DESC,
mtr.transaction_id DESC
)
WHERE
ROWNUM = 1;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fnunitcostondate;
————————————————————————————-
–To find the end date Unit cost.
————————————————————————————-
FUNCTION fnunitcoston_enddate (
p_organization_id NUMBER,
p_subinventory_code VARCHAR2,
p_locator_id NUMBER,
p_inventory_item_id NUMBER,
p_end_date DATE
) RETURN NUMBER IS
lv_result NUMBER;
BEGIN
SELECT
actual_cost
INTO lv_result
FROM
(
SELECT
mcd.new_cost actual_cost
FROM
mtl_cst_actual_cost_details mcd,
apps.mtl_material_transactions mtr,
mtl_transaction_types mtt
WHERE
1 = 1
AND mcd.transaction_id = mtr.transaction_id
AND mtr.organization_id = p_organization_id
AND mcd.inventory_item_id = mtr.inventory_item_id
AND mcd.organization_id = mtr.organization_id
AND mtr.inventory_item_id = p_inventory_item_id
AND mtr.transaction_type_id = mtt.transaction_type_id
— AND MTT.transaction_type_name IN (‘Sales Order Pick’,’Sales order issue’,’PO Receipt’,’Legacy Opening Stock’)
AND trunc(mtr.transaction_date) <= to_char(p_end_date, ‘DD-MON-YYYY’) –AND to_char(p_end_date, ‘DD-MON-YYYY’)
ORDER BY
mcd.transaction_costed_date DESC,
mtr.transaction_id DESC
)
WHERE
ROWNUM = 1;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fnunitcoston_enddate;
—————————————————————————
— To get SOH on specific date
—————————————————————————
FUNCTION fnsohhistory (
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_subinventory_code VARCHAR2,
p_start_date DATE
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT
SUM(target_qty)
INTO lv_result
FROM
(
SELECT
moqv.subinventory_code subinv,
moqv.inventory_item_id item_id,
SUM(transaction_quantity) target_qty
FROM
mtl_onhand_qty_cost_v moqv
WHERE
moqv.organization_id = p_organization_id
AND moqv.inventory_item_id = p_inventory_item_id
AND moqv.subinventory_code = nvl(p_subinventory_code, moqv.subinventory_code)
GROUP BY
moqv.subinventory_code,
moqv.inventory_item_id,
moqv.item_cost
UNION ALL
SELECT
mmt.subinventory_code subinv,
mmt.inventory_item_id item_id,
– SUM(primary_quantity) target_qty
FROM
mtl_material_transactions mmt,
mtl_txn_source_types mtst
WHERE
mmt.organization_id = p_organization_id
AND trunc(mmt.transaction_date) >= to_char(p_start_date, ‘DD-MON-YYYY’)
AND mmt.transaction_source_type_id = mtst.transaction_source_type_id
AND mmt.inventory_item_id = p_inventory_item_id
AND mmt.subinventory_code = nvl(p_subinventory_code, mmt.subinventory_code)
GROUP BY
mmt.subinventory_code,
mmt.inventory_item_id
) oq
GROUP BY
oq.item_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fnsohhistory;
FUNCTION fnenddatesohhis (
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_subinventory_code VARCHAR2,
p_start_date DATE,
p_end_date DATE
) RETURN NUMBER IS
lv_result NUMBER;
BEGIN
SELECT
SUM(primary_quantity) target_qty
INTO lv_result
FROM
mtl_material_transactions mmt
— mtl_txn_source_types mtst
INNER JOIN apps.mtl_transaction_types mtt ON mtt.transaction_type_id = mmt.transaction_type_id
WHERE
mmt.organization_id = p_organization_id
AND trunc(transaction_date) >= to_char(p_start_date, ‘DD-MON-YYYY’)
AND trunc(transaction_date) <= to_char(p_end_date, ‘DD-MON-YYYY’)
— AND mmt.transaction_source_type_id = mtst.transaction_source_type_id
AND mmt.inventory_item_id = p_inventory_item_id
AND mmt.subinventory_code = nvl(p_subinventory_code, mmt.subinventory_code)
;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fnenddatesohhis;
FUNCTION fnnumber (
p_number NUMBER
) RETURN VARCHAR2 IS
lv_result VARCHAR2(100);
BEGIN
IF MOD(nvl(p_number, 0), 1) != 0 THEN
lv_result := ( to_char(p_number, ‘FM999G999G999G990D999’) );
— lv_result := (to_char(p_number, ‘999G999G999G999G990D0000’));
ELSE
lv_result := ( to_char(p_number, ‘999G999G999G999G990’) );
END IF;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN p_number;
END fnnumber;
FUNCTION fnamountapplied (
p_invoice_id NUMBER
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT
– 1 * SUM(nvl(ida2.amount, 0))
INTO lv_result
FROM
ap_invoice_distributions_all ida1,
apps.ap_invoices_all aia1,
ap_invoice_distributions_all ida2
WHERE
1 = 1
AND ( aia1.invoice_id = ida1.invoice_id )
AND p_invoice_id = ida2.invoice_id
AND ida2.line_type_lookup_code = ‘PREPAY’
AND ida1.invoice_distribution_id = ida2.prepay_distribution_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fnamountapplied;
———————————————————————————
— Function to get Po batch GL number
———————————————————————————
FUNCTION fn_gl_batch (
p_invoice_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(3000);
BEGIN
SELECT
LISTAGG(r.doc_sequence_value, ‘,’) WITHIN GROUP(
ORDER BY
r.doc_sequence_value ASC
)
INTO lv_result
FROM
(
SELECT DISTINCT
aia.invoice_num,
gjh.doc_sequence_value
FROM
xla_ae_headers xah,
xla_ae_lines xal,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
(
SELECT
*
FROM
xla.xla_transaction_entities
WHERE
application_id = 200
AND entity_code = ‘AP_INVOICES’
) xte,
ap_invoices_all aia
— ,ap_invoice_payments_all aipa,
— ap_invoice_distributions_all aida,
— apps.ap_invoice_lines_all aila
WHERE
gjh.je_header_id = gir.je_header_id
AND xah.ae_header_id = xal.ae_header_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.je_header_id = gjl.je_header_id
AND gjh.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND nvl(xte.source_id_int_1,(- 99)) = ( aia.invoice_id )–to_char
AND xah.je_category_name = ‘Purchase Invoices’
AND xte.entity_id = xah.entity_id
— AND aia.invoice_id = aipa.invoice_id
— AND aia.invoice_id = aila.invoice_id
AND aia.invoice_id = p_invoice_id
— AND aia.invoice_id = aida.invoice_id
— AND aida.invoice_line_number = aila.line_number
AND aia.accts_pay_code_combination_id = gjl.code_combination_id
) r
GROUP BY
r.invoice_num;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fn_gl_batch;
—————————————————————————
— To get Applied Prepayment value
—————————————————————————
FUNCTION fnprepaymentapplied (
p_invoice NUMBER
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT
SUM(nvl(ida2.amount, 0))
INTO lv_result
FROM
ap_invoice_distributions_all ida1,
apps.ap_invoices_all aia1,
ap_invoice_distributions_all ida2
WHERE
1 = 1
AND ( aia1.invoice_id = ida1.invoice_id )
AND p_invoice = aia1.invoice_id
AND ida2.line_type_lookup_code = ‘PREPAY’
AND ida1.invoice_distribution_id = ida2.prepay_distribution_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fnprepaymentapplied;
———————————————————————————
— Function to get GL transfer
———————————————————————————
FUNCTION fn_gl_transfer (
p_invoice_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(3000);
BEGIN
SELECT
LISTAGG(r.status, ‘,’) WITHIN GROUP(
ORDER BY
r.status ASC
)
INTO lv_result
FROM
(
SELECT DISTINCT
aia.invoice_num,
decode(gjh.status, ‘P’, ‘POSTED’, ‘U’, ‘UNPOSTED’,
‘S’, ‘SELECT’, ‘I’, ‘IN THE PROCESS OF BEING POSTED’, gjh.status) status
FROM
xla_ae_headers xah,
xla_ae_lines xal,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
(
SELECT
*
FROM
xla.xla_transaction_entities
WHERE
application_id = 200
AND entity_code = ‘AP_INVOICES’
) xte,
ap_invoices_all aia
— ,ap_invoice_payments_all aipa,
— ap_invoice_distributions_all aida,
— apps.ap_invoice_lines_all aila
WHERE
gjh.je_header_id = gir.je_header_id
AND xah.ae_header_id = xal.ae_header_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.je_header_id = gjl.je_header_id
AND gjh.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND nvl(xte.source_id_int_1,(- 99)) = ( aia.invoice_id )–to_char
AND xah.je_category_name = ‘Purchase Invoices’
AND xte.entity_id = xah.entity_id
— AND aia.invoice_id = aipa.invoice_id
— AND aia.invoice_id = aila.invoice_id
AND aia.invoice_id = p_invoice_id
— AND aia.invoice_id = aida.invoice_id
— AND aida.invoice_line_number = aila.line_number
AND aia.accts_pay_code_combination_id = gjl.code_combination_id
) r
GROUP BY
r.invoice_num;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fn_gl_transfer;
———————————————————————————
— Function to get GL transfer status
———————————————————————————
FUNCTION fn_gl_transfer_status (
p_invoice_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(3000);
BEGIN
SELECT DISTINCT
xlk3.meaning –decode( gjh.STATUS,’P’,’POSTED’,’U’,’UNPOSTED’,’S’,’SELECT’,’I’,’IN THE PROCESS OF BEING POSTED’,gjh.STATUS) STATUS
INTO lv_result
FROM
xla_ae_headers xah,
xla_lookups xlk3,
(
SELECT
*
FROM
xla.xla_transaction_entities
WHERE
application_id = 200
AND entity_code = ‘AP_INVOICES’
) xte
WHERE
1 = 1
AND nvl(xte.source_id_int_1,(- 99)) = ( p_invoice_id )–to_char
AND xah.je_category_name = ‘Purchase Invoices’
AND xte.entity_id = xah.entity_id
AND xlk3.lookup_code (+) = xah.gl_transfer_status_code
AND xlk3.lookup_type (+) = ‘GL_TRANSFER_FLAG’;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fn_gl_transfer_status;
—————————————————————————
— To get SOH on specific date
—————————————————————————
FUNCTION fnsohsub (
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_subinventory_code VARCHAR2,
p_start_date DATE
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(50);
BEGIN
SELECT
subinv
INTO lv_result
FROM
(
SELECT
moqv.subinventory_code subinv,
moqv.inventory_item_id item_id
— SUM(transaction_quantity) target_qty
FROM
mtl_onhand_qty_cost_v moqv
WHERE
moqv.organization_id = p_organization_id
AND moqv.inventory_item_id = p_inventory_item_id
AND moqv.subinventory_code = nvl(p_subinventory_code, moqv.subinventory_code)
— GROUP BY
— moqv.subinventory_code,
— moqv.inventory_item_id,
— moqv.item_cost
UNION ALL
SELECT
mmt.subinventory_code subinv,
mmt.inventory_item_id item_id
— – SUM(primary_quantity) target_qty
FROM
mtl_material_transactions mmt,
mtl_txn_source_types mtst
— left join APPS.MTL_TRANSACTION_TYPES MTT on MTT.TRANSACTION_TYPE_ID=mmt.TRANSACTION_TYPE_ID
WHERE
mmt.organization_id = p_organization_id
AND trunc(mmt.transaction_date) >= to_char(p_start_date, ‘DD-MON-YYYY’)
— AND trunc(mmt.transaction_date) <= to_char(p_start_date, ‘DD-MON-YYYY’)
AND mmt.transaction_source_type_id = mtst.transaction_source_type_id
AND mmt.inventory_item_id = p_inventory_item_id
AND mmt.subinventory_code = nvl(p_subinventory_code, mmt.subinventory_code)
— AND MTT.transaction_type_name NOT IN ( ‘Miscellaneous Indirect Material Cost Receipt’, ‘Subinventory Transfer’, ‘Sales Order Pick’,
— ‘Internal Order Pick’, ‘Move Order Transfer’,
— ‘Miscellaneous Recpt(SCNE)’, ‘Miscellaneous Recpt(SCEX)’, ‘Miscellaneous Cyl Cost Receipt’,
— ‘Miscellaneous Packing Cost Receipt’, ‘COGS Recognition’,
— ‘WIP Byproduct Completion’, ‘Residual Qty Issue’, ‘Residual Qty Receipt’, ‘Miscellaneous Copper Scrap Receipt’,
— ‘WIP Byproduct Return’ )
— GROUP BY
— mmt.subinventory_code,
— mmt.inventory_item_id
) oq
WHERE
ROWNUM = 1
AND subinv IS NOT NULL;
— GROUP BY
— oq.item_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fnsohsub;
—————————————————————————
— To get lookup meaning
—————————————————————————
FUNCTION fn_mfg_lookups (
p_lookup_type VARCHAR2,
p_lookup_code VARCHAR2
) RETURN VARCHAR2 IS
lv_result VARCHAR2(200);
BEGIN
SELECT
meaning flv
INTO lv_result
FROM
mfg_lookups flv
WHERE
lookup_type = p_lookup_type
AND flv.lookup_code = p_lookup_code;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fn_mfg_lookups;
—————————————————————————
— To get categoriries
—————————————————————————
FUNCTION fn_categories (
p_inventory_item_id NUMBER,
p_organization_id NUMBER,
p_category_set_name VARCHAR2
) RETURN VARCHAR2 IS
lv_result VARCHAR2(200);
BEGIN
SELECT
cv.segment1
|| nvl2(cv.segment2, ‘.’ || cv.segment2, cv.segment2)
|| nvl2(cv.segment3, ‘.’ || cv.segment3, cv.segment3)
INTO lv_result
FROM
inv.mtl_item_categories ic
INNER JOIN inv.mtl_category_sets_tl st ON st.category_set_id = ic.category_set_id
AND st.language = ‘US’
AND st.category_set_name = p_category_set_name–( ‘AU Source of Manufacturing’ )–,’HAF_ARL_Types’
LEFT OUTER JOIN inv.mtl_categories_b cv ON cv.category_id = ic.category_id
LEFT OUTER JOIN inv.mtl_parameters iorg ON iorg.organization_id = ic.organization_id
WHERE
iorg.organization_id = p_organization_id
AND ic.inventory_item_id = p_inventory_item_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fn_categories;
—————————————————————————
— To get Customer Cross reference info
————————————————————————–
FUNCTION fncustxresfsinfo (
p_inventory_item_id NUMBER,
p_type VARCHAR2
) RETURN VARCHAR2 IS
lv_result VARCHAR2(200);
BEGIN
IF p_type = ‘customer_group’ THEN
SELECT
hzc.attribute7
INTO lv_result
FROM
apps.mtl_customer_item_xrefs cix –ON msi.inventory_item_id = cix.inventory_item_id and INACTIVE_FLAG=’N’
JOIN apps.mtl_customer_items ci ON cix.customer_item_id = ci.customer_item_id
JOIN apps.hz_cust_accounts_all ca ON ci.customer_id = ca.cust_account_id
JOIN hz_cust_accounts hzc ON hzc.cust_account_id = ca.cust_account_id
JOIN hz_parties hp ON ( hzc.party_id = hp.party_id
AND hzc.status = ‘A’ )
WHERE
p_inventory_item_id = cix.inventory_item_id
AND cix.inactive_flag = ‘N’;
ELSIF p_type = ‘customer_no’ THEN
SELECT
hzc.account_number
INTO lv_result
FROM
apps.mtl_customer_item_xrefs cix –ON msi.inventory_item_id = cix.inventory_item_id and INACTIVE_FLAG=’N’
JOIN apps.mtl_customer_items ci ON cix.customer_item_id = ci.customer_item_id
JOIN apps.hz_cust_accounts_all ca ON ci.customer_id = ca.cust_account_id
JOIN hz_cust_accounts hzc ON hzc.cust_account_id = ca.cust_account_id
JOIN hz_parties hp ON ( hzc.party_id = hp.party_id
AND hzc.status = ‘A’ )
WHERE
p_inventory_item_id = cix.inventory_item_id
AND cix.inactive_flag = ‘N’;
ELSIF p_type = ‘customer_name’ THEN
SELECT
hp.party_name
INTO lv_result
FROM
apps.mtl_customer_item_xrefs cix –ON msi.inventory_item_id = cix.inventory_item_id and INACTIVE_FLAG=’N’
JOIN apps.mtl_customer_items ci ON cix.customer_item_id = ci.customer_item_id
JOIN apps.hz_cust_accounts_all ca ON ci.customer_id = ca.cust_account_id
JOIN hz_cust_accounts hzc ON hzc.cust_account_id = ca.cust_account_id
JOIN hz_parties hp ON ( hzc.party_id = hp.party_id
AND hzc.status = ‘A’ )
WHERE
p_inventory_item_id = cix.inventory_item_id
AND cix.inactive_flag = ‘N’;
END IF;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fncustxresfsinfo;
—————————————————————————
— To get variance amt
—————————————————————————
FUNCTION fn_variance_amt (
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_from_date VARCHAR2,
p_to_date VARCHAR2
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT
— mmt.*
SUM(mta.base_transaction_value) –SUM(variance_amount)
INTO lv_result
FROM
mtl_material_transactions mmt,
mtl_transaction_accounts mta,
gl_code_combinations gcc
WHERE
1 = 1
AND mmt.organization_id = p_organization_id–221
AND mmt.inventory_item_id = p_inventory_item_id–1396294
AND mmt.transaction_id = mta.transaction_id
AND gcc.code_combination_id = mta.reference_account
AND gcc.segment3 = 12220
AND gcc.segment4 = 1105
AND trunc(mmt.transaction_date) BETWEEN to_date(p_from_date, ‘DD-MON-YYYY’) AND to_date(p_to_date, ‘DD-MON-YYYY’);
RETURN nvl(lv_result, 0);
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fn_variance_amt;
————————————————————————————-
–To get credit no
————————————————————————————-
FUNCTION fn_creditno (
p_customer_trx_id NUMBER,
p_type VARCHAR2
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(500);
BEGIN
IF p_type = ‘credit_no’ THEN
BEGIN
SELECT
LISTAGG(trx_number, ‘,’) WITHIN GROUP(
ORDER BY
araa.customer_trx_id
)
INTO lv_result
FROM
ra_customer_trx_all a,
ar_receivable_applications_all araa
WHERE
a.customer_trx_id = araa.customer_trx_id
AND araa.applied_customer_trx_id = p_customer_trx_id
AND araa.customer_trx_id IS NOT NULL;
— RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
lv_result := NULL;
END;
ELSIF p_type = ‘total_credit_amt_doc’ THEN
BEGIN
SELECT
– 1 * SUM(araa.amount_applied) –line_applied
INTO lv_result
FROM
ar_receivable_applications_all araa
WHERE
araa.applied_customer_trx_id = p_customer_trx_id
AND araa.customer_trx_id IS NOT NULL;
— RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
lv_result := ‘0’;
END;
ELSIF p_type = ‘credit_application_date’ THEN
BEGIN
SELECT
LISTAGG(to_char(araa.apply_date, ‘DD-MON-YYYY’), ‘,’) WITHIN GROUP(
ORDER BY
araa.customer_trx_id
)
INTO lv_result
FROM
ar_receivable_applications_all araa
WHERE
araa.applied_customer_trx_id = p_customer_trx_id
AND araa.customer_trx_id IS NOT NULL;
— RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
lv_result := NULL;
END;
ELSIF p_type = ‘credit_date’ THEN
BEGIN
SELECT
LISTAGG(to_char(araa.gl_date, ‘DD-MON-YYYY’), ‘,’) WITHIN GROUP(
ORDER BY
araa.customer_trx_id
)
INTO lv_result
FROM
ar_receivable_applications_all araa
WHERE
araa.applied_customer_trx_id = p_customer_trx_id
AND araa.customer_trx_id IS NOT NULL;
— RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
lv_result := NULL;
END;
ELSIF p_type = ‘adjustment_number’ THEN
BEGIN
SELECT
LISTAGG(adjustment_number, ‘,’) WITHIN GROUP(
ORDER BY
customer_trx_id
)
INTO lv_result
FROM
(
SELECT DISTINCT
ct.adjustment_number,
ct.customer_trx_id
FROM
ar_adjustments_all ct
WHERE
p_customer_trx_id = ct.customer_trx_id
) a;
— RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
lv_result := NULL;
END;
ELSIF p_type = ‘apply_date’ THEN
BEGIN
SELECT
LISTAGG(to_char(apply_date, ‘DD-MON-YYYY’), ‘,’) WITHIN GROUP(
ORDER BY
customer_trx_id
)
INTO lv_result
FROM
(
SELECT DISTINCT
ct.apply_date,
ct.customer_trx_id
FROM
ar_adjustments_all ct
WHERE
p_customer_trx_id = ct.customer_trx_id
) a;
— RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
lv_result := NULL;
END;
ELSIF p_type = ‘amount’ THEN
BEGIN
SELECT
SUM(amount)
INTO lv_result
FROM
ar_adjustments_all ct
WHERE
p_customer_trx_id = ct.customer_trx_id
GROUP BY
ct.customer_trx_id;
— RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
lv_result := 0;
END;
ELSIF p_type = ‘CM_total_credit_amt_doc’ THEN
BEGIN
SELECT
SUM(araa.amount_applied) –line_applied
INTO lv_result
FROM
— ra_customer_trx_all a,
ar_receivable_applications_all araa
–ra_customer_trx_all b
WHERE
p_customer_trx_id = araa.customer_trx_id;
— AND araa.applied_customer_trx_id = b.customer_trx_id;
— RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
lv_result := NULL;
END;
ELSIF p_type = ‘CM_credit_no’ THEN
BEGIN
SELECT
LISTAGG(trx_number, ‘,’) WITHIN GROUP(
ORDER BY
trx_number
)
–line_applied
INTO lv_result
FROM
(
SELECT DISTINCT
b.trx_number
FROM
— ra_customer_trx_all a,
ar_receivable_applications_all araa,
ra_customer_trx_all b
WHERE
p_customer_trx_id = araa.customer_trx_id
AND araa.applied_customer_trx_id = b.customer_trx_id
);
— RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
lv_result := NULL;
END;
ELSIF p_type = ‘CM_credit_application_date’ THEN
BEGIN
SELECT
LISTAGG(to_char(apply_date, ‘DD-MON-YYYY’), ‘,’) WITHIN GROUP(
ORDER BY
apply_date
)
–line_applied
INTO lv_result
FROM
(
SELECT DISTINCT
araa.apply_date
FROM
— ra_customer_trx_all a,
ar_receivable_applications_all araa
— ,ra_customer_trx_all b
WHERE
p_customer_trx_id = araa.customer_trx_id
— AND araa.applied_customer_trx_id = b.customer_trx_id
);
— RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
lv_result := NULL;
END;
ELSIF p_type = ‘CM_credit_date’ THEN
BEGIN
SELECT
LISTAGG(to_char(gl_date, ‘DD-MON-YYYY’), ‘,’) WITHIN GROUP(
ORDER BY
gl_date
)
–line_applied
INTO lv_result
FROM
(
SELECT DISTINCT
araa.gl_date
FROM
— ra_customer_trx_all a,
ar_receivable_applications_all araa
— ,ra_customer_trx_all b
WHERE
p_customer_trx_id = araa.customer_trx_id
— AND araa.applied_customer_trx_id = b.customer_trx_id
);
— RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
lv_result := NULL;
END;
END IF;
RETURN lv_result;
END fn_creditno;
————————————————————————————-
–To find the end date Unit cost.
————————————————————————————-
FUNCTION fnarpost (
p_trx_number VARCHAR2,
p_cust_trx_line_gl_dist_id NUMBER,
p_code_combination_id NUMBER,
p_event_id NUMBER
) RETURN VARCHAR2
RESULT_CACHE
IS
lv_result VARCHAR2(100);
BEGIN
SELECT DISTINCT
decode(gjl.status, ‘P’, ‘Posted’, ‘U’, ‘Unposted’,
gjl.status)
INTO lv_result
FROM
apps.xla_distribution_links xdl,
apps.xla_ae_lines xal,
apps.gl_je_lines gjl,
apps.gl_je_headers gjh,
apps.gl_je_batches gjb,
apps.gl_import_references gir
WHERE
1 = 1
— rctlgd.customer_trx_id = rct.customer_trx_id
— AND rctlgd.customer_trx_line_id = rctl.customer_trx_line_id (+)
— AND rct.cust_trx_type_id = rctt.cust_trx_type_id (+)
— AND rct.bill_to_customer_id = hca.cust_account_id
— AND hca.party_id = hp.party_id
— AND rct.batch_source_id = rbs.batch_source_id
AND xdl.application_id = xal.application_id
AND xal.application_id = 222
AND xdl.event_id = p_event_id –rctlgd.event_id
— AND xdl.source_distribution_id_num_1 = p_cust_trx_line_gl_dist_id–rctlgd.cust_trx_line_gl_dist_id
AND xdl.rounding_class_code = ‘RECEIVABLE’
AND xdl.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xal.code_combination_id = p_code_combination_id– gcc.code_combination_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xdl.event_id = p_event_id
–AND rctlgd.gl_posted_date IS NULL and ACCOUNT_CLASS=’REV’
AND xdl.source_distribution_id_num_1 = p_cust_trx_line_gl_dist_id;
— AND rct.trx_number = p_trx_number;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN ‘UnPosted’;
END fnarpost;
FUNCTION fnreceiptpoquantity (
po_number NUMBER,
p_inventory_item_id NUMBER,
p_org_id NUMBER,
p_type VARCHAR2
) RETURN NUMBER IS
lv_result NUMBER;
BEGIN
IF p_type = ‘PO_quantity’ THEN
SELECT
— ( SUM(nvl(plla.quantity, 0)) – SUM(nvl(plla.quantity_received, 0)) – SUM(nvl(plla.quantity_cancelled, 0)) – SUM(nvl(plla.
— quantity_rejected, 0)) )
SUM(nvl(plla.quantity, 0)) – SUM(nvl(plla.quantity_cancelled, 0))
INTO lv_result
FROM
po_headers_all ph
LEFT JOIN po_lines_all pl ON ph.po_header_id = pl.po_header_id
AND pl.item_id = p_inventory_item_id
LEFT JOIN hr_locations_all hla ON hla.location_id = ph.ship_to_location_id
LEFT JOIN po_line_locations_all plla ON pl.po_line_id = plla.po_line_id
WHERE
1 = 1
AND ph.po_header_id = po_number
—1395773
— AND nvl(pl.closed_code, ‘OPEN’) = ‘OPEN’
— AND ph.closed_code = ‘OPEN’
AND inventory_organization_id = p_org_id;
ELSIF p_type = ‘Invoiced’ THEN
SELECT
SUM(nvl(plla.quantity_billed, 0))
INTO lv_result
FROM
po_headers_all ph
LEFT JOIN po_lines_all pl ON ph.po_header_id = pl.po_header_id
AND pl.item_id = p_inventory_item_id
LEFT JOIN hr_locations_all hla ON hla.location_id = ph.ship_to_location_id
LEFT JOIN po_line_locations_all plla ON pl.po_line_id = plla.po_line_id
WHERE
1 = 1
AND ph.po_header_id = po_number
—1395773
— AND nvl(pl.closed_code, ‘OPEN’) = ‘OPEN’
— AND ph.closed_code = ‘OPEN’
AND inventory_organization_id = p_org_id;
END IF;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fnreceiptpoquantity;
FUNCTION fnvariancebycustomer (
p_organization_id NUMBER,
p_sold_to_org_id NUMBER,
p_from_date VARCHAR2,
p_to_date VARCHAR2,
p_type VARCHAR2 DEFAULT NULL
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT
SUM(mta.base_transaction_value)
INTO lv_result
FROM
mtl_material_transactions mmt,
mtl_transaction_accounts mta,
gl_code_combinations gcc,
(
SELECT DISTINCT
hca.cust_account_id,
msi.inventory_item_id,
msi.organization_id
FROM
(
SELECT
*
FROM
mtl_system_items_b
WHERE
organization_id = p_organization_id
) msi
LEFT OUTER JOIN apps.mtl_customer_item_xrefs cix ON msi.inventory_item_id = cix.inventory_item_id
AND cix.inactive_flag = ‘N’
LEFT OUTER JOIN apps.mtl_customer_items ci ON cix.customer_item_id = ci.customer_item_id
LEFT OUTER JOIN apps.hz_cust_accounts_all ca ON ci.customer_id = ca.cust_account_id
JOIN hz_cust_accounts hca ON hca.cust_account_id = ca.cust_account_id
AND hca.cust_account_id = p_sold_to_org_id
LEFT OUTER JOIN hz_parties hp ON ( hca.party_id = hp.party_id
AND hca.status = ‘A’ )
) cust_item
WHERE
1 = 1
AND mmt.organization_id = cust_item.organization_id
AND mmt.transaction_id = mta.transaction_id
AND gcc.code_combination_id = mta.reference_account
AND mmt.inventory_item_id = cust_item.inventory_item_id
AND gcc.segment3 = ‘12220’
AND gcc.segment4 = ‘1105’
AND trunc(mmt.transaction_date) BETWEEN to_date(p_from_date, ‘DD-MON-YYYY’) AND to_date(p_to_date, ‘DD-MON-YYYY’);
RETURN nvl(lv_result, 0);
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fnvariancebycustomer;
END xxa_pkgutil;

Step 2:

SQL Report Code:

SELECT
msi.segment1 item,
nvl(vw2.description, msi.description) item_description,
vw2.qty_sold sales_quantity,
xxa_pkgutil.fnfdsameclass_con_rate(msi.inventory_item_id, ‘M2’) / xxa_pkgutil.fnfindbaseuomconversion(msi.primary_uom_code) sqm_conv_sqm_per_uom,
round(vw2.qty_sold /(xxa_pkgutil.fnfdsameclass_con_rate(msi.inventory_item_id, ‘M2’) / xxa_pkgutil.fnfindbaseuomconversion(msi.primary_uom_code)),
5) sales_qty_sqm,
vw2.doc_currency doc_currency,
xxa_pkgutil.sfgetcustcode(vw2.sold_to_org_id) customer_code, — add on 18jan23
(
SELECT
MAX(ci.customer_item_number)
FROM
apps.mtl_customer_item_xrefs cix
LEFT OUTER JOIN apps.mtl_customer_items ci ON ci.customer_item_id = cix.customer_item_id
WHERE
cix.inventory_item_id = msi.inventory_item_id
) customer_item, — add on 18jan23
xxa_pkgutil.sfgetpartyname(vw2.sold_to_org_id) customer_name,
msi.primary_uom_code uom,
hp.attribute5 “Customer Market Segment”,
hp.attribute6 “Customer Type”,
hca.attribute7 “Customer GKA Type”,
hp.attribute8 “GKA subclass”,
(
SELECT
—
MAX(fl.description)
FROM
mtl_item_categories mic,
mtl_categories_b mcb,
mtl_category_sets_tl mcst,
apps.fnd_flex_values_vl fl
WHERE
msi.inventory_item_id = mic.inventory_item_id
AND mic.category_id (+) = mcb.category_id
AND mic.category_set_id = mcst.category_set_id
AND fl.flex_value = mcb.segment2
AND mcst.category_set_name = ‘Inventory’
AND mcb.segment1 = ‘Flex_FG’
AND mic.organization_id = msi.organization_id
) “Packaging type”,
(
SELECT
MAX(fl.description)
FROM
mtl_item_categories mic,
mtl_categories_b mcb,
mtl_category_sets_tl mcst,
apps.fnd_flex_values_vl fl
WHERE
msi.inventory_item_id = mic.inventory_item_id
AND mic.category_id (+) = mcb.category_id
AND mic.category_set_id = mcst.category_set_id
AND fl.flex_value = mcb.segment5
AND mcst.category_set_name = ‘Inventory’
AND mcb.segment1 = ‘Flex_FG’
AND mic.organization_id = msi.organization_id
AND fl.description IS NOT NULL
) “Manufacturing origin”,
(
SELECT
MAX(fl.description)
FROM
inv.mtl_item_categories ic
INNER JOIN inv.mtl_category_sets_tl st ON st.category_set_id = ic.category_set_id
AND st.language = ‘US’
AND st.category_set_name IN ( ‘JWC_MFG_CATEGORIES’ )–,’DOY_ARL_Types’
LEFT OUTER JOIN inv.mtl_categories_b cv ON cv.category_id = ic.category_id
LEFT OUTER JOIN inv.mtl_parameters iorg ON iorg.organization_id = ic.organization_id
LEFT OUTER JOIN apps.fnd_flex_values_vl fl ON fl.flex_value = cv.segment1
AND fl.description IS NOT NULL
WHERE ic.inventory_item_id = msi.inventory_item_id
AND ic.organization_id = msi.organization_id
) “Market segment”,
(
SELECT
MAX(fl.description) –CV.SEGMENT2
FROM
inv.mtl_item_categories ic
INNER JOIN inv.mtl_category_sets_tl st ON st.category_set_id = ic.category_set_id
AND st.language = ‘US’
AND st.category_set_name IN ( ‘JWC_MFG_CATEGORIES’ )–,’DOY_ARL_Types’
LEFT OUTER JOIN inv.mtl_categories_b cv ON cv.category_id = ic.category_id
LEFT OUTER JOIN inv.mtl_parameters iorg ON iorg.organization_id = ic.organization_id
LEFT OUTER JOIN apps.fnd_flex_values_vl fl ON fl.flex_value = cv.segment2
AND fl.description IS NOT NULL
WHERE ic.inventory_item_id = msi.inventory_item_id
AND ic.organization_id = msi.organization_id
) “Market SubSegment”,
msi.attribute26 “Item_Structure”,
(
SELECT
cv.segment1
FROM
inv.mtl_item_categories ic
INNER JOIN inv.mtl_category_sets_tl st ON st.category_set_id = ic.category_set_id
AND st.language = ‘US’
AND st.category_set_name IN ( ‘Flex_ARL_Types’ )
LEFT OUTER JOIN inv.mtl_categories_b cv ON cv.category_id = ic.category_id
LEFT OUTER JOIN inv.mtl_parameters iorg ON iorg.organization_id = ic.organization_id
WHERE
ic.inventory_item_id = msi.inventory_item_id–1555037–1396367
AND ic.organization_id = msi.organization_id–221
) “ARL_Types”,
msi.attribute28 “PCR”,
round(revenue_doc, 2) net_sales_amt_doc,
round(qty_sold /(xxa_pkgutil.fnfdsameclass_con_rate(msi.inventory_item_id, ‘M2’) / xxa_pkgutil.fnfindbaseuomconversion(msi.primary_uom_code)),
2) fx_rate,
round(revenue_loc, 2) net_sales_amt_loc,
cost_of_sales “Cost of Sales”,
round(cogs_variance, 2) cogs_variance,
margin,
margin_percentage
FROM
(
SELECT
sold_to_org_id,
ship_from_org_id,
inventory_item_id,
doc_currency,
description,
cogs_variance,
SUM(qty_sold) qty_sold,
SUM(revenue) revenue_doc,
SUM(cost_of_sales) cost_of_sales,
round(SUM(nvl(revenue_loc, 0)) – SUM(nvl(cost_of_sales, 0)) – cogs_variance, 2) margin,
CASE
WHEN SUM(nvl(revenue_loc, 0)) = 0 THEN
‘0’ || ‘%’
ELSE
round(100 *(1 -((SUM(nvl(cost_of_sales, 0)) + cogs_variance) / SUM(nvl(revenue_loc, 0)))), 2)
|| ‘%’
END margin_percentage,
round(SUM(revenue_loc), 2) revenue_loc
FROM
(
SELECT
x.sold_to_org_id,
x.description,
x.ship_from_org_id,
x.inventory_item_id,
CASE
WHEN xxa_pkgutil.fntrxtype(cust_trx_type_id) IN ( ‘DOY Invoice’, ‘DOY Other’ ) THEN
nvl(x.sales_quantity, 0)
WHEN xxa_pkgutil.fntrxtype(cust_trx_type_id) IN ( ‘DOY Credit Note’, ‘DOY Other CM’ ) THEN
– 1 * nvl(x.return_quantity, 0)
ELSE
nvl(x.sales_quantity, 0) – nvl(x.return_quantity, 0)
END qty_sold,
x.amount revenue,
CASE
WHEN xxa_pkgutil.fntrxtype(cust_trx_type_id) IN ( ‘DOY Invoice’, ‘DOY Other’ ) THEN
round(nvl(x.sales_quantity, 0) *(xxa_pkgutil.fntranactualcost(line_id)–, cic.item_cost
), 2)
WHEN xxa_pkgutil.fntrxtype(cust_trx_type_id) IN ( ‘DOY Credit Note’, ‘DOY Other CM’ ) THEN
– 1 * round(nvl(x.return_quantity, 0) *(xxa_pkgutil.fntranactualcost(nvl(reference_line_id, line_id))
–,cic.item_cost
), 2)
ELSE
round((nvl(x.sales_quantity, 0) – nvl(x.return_quantity, 0)) *(xxa_pkgutil.fntranactualcost(line_id)
— ,cic.item_cost
), 2)
END cost_of_sales,
xxa_pkgutil.sfgetcurrate(x.document, ‘CUR’) doc_currency,
nvl(xxa_pkgutil.sfgetexchangerate(x.document), 1) exchange_rate,
round(x.amount * nvl(xxa_pkgutil.sfgetexchangerate(x.document), 1), 2) revenue_loc,
xxa_pkgutil.fn_variance_amt(p_organization_id => :p52_inventory_org, p_inventory_item_id => x.inventory_item_id, p_from_date => :
p52_from_date, p_to_date => :p52_to_date) cogs_variance,
gl_date
FROM
(
SELECT DISTINCT
inventory_item_id,
description,
sold_to_org_id,
ship_from_org_id,
order_number,
trx_number document,
trx_date document_date,
unit_price,
SUM(shipped_quantity) sales_quantity,
SUM(return_quantity) return_quantity,
SUM(c_extended_amount) sales_amount,
SUM(r_extended_amount) return_amount,
line_id,
reference_line_id,
cust_trx_type_id,
amount,
code_combination_id,
batch_source_id,
org_id,
gl_date,
purchase_order
FROM
(
SELECT
oola.inventory_item_id,
NULL description,
ooha.sold_to_org_id,
oola.ship_from_org_id,
ooha.order_number order_number,
rt.trx_number trx_number,
oola.unit_selling_price unit_price,
rt.trx_date,
CASE
WHEN xxa_pkgutil.fntrxtype(rt.cust_trx_type_id) IN ( ‘DOY Invoice’, ‘DOY Other’ ) THEN
oola.shipped_quantity + nvl(rsl.quantity_received, 0)
ELSE
oola.shipped_quantity
END shipped_quantity,
nvl(rsl.quantity_received, 0) return_quantity,
oola.shipped_quantity * oola.unit_selling_price c_extended_amount,
nvl(rsl.quantity_received, 0) * oola.unit_selling_price r_extended_amount,
— mmt.actual_cost actual_cost
oola.line_id,
oola.reference_line_id,
rt.cust_trx_type_id,
rcg.amount,
rcg.code_combination_id,
rt.batch_source_id,
rt.org_id,
rcg.gl_date,
rt.purchase_order
FROM
apps.oe_order_headers_all ooha
JOIN apps.oe_order_lines_all oola ON ( ooha.header_id = oola.header_id )
LEFT JOIN apps.rcv_shipment_lines rsl ON ( ooha.header_id = rsl.oe_order_header_id
AND rsl.oe_order_line_id = oola.line_id )
LEFT JOIN apps.rcv_shipment_headers rsh ON ( rsh.shipment_header_id = rsl.shipment_header_id
AND source_document_code = ‘RMA’ )
LEFT JOIN ra_customer_trx_all rt ON ( ( rt.interface_header_attribute1 ) = to_char(ooha.
order_number) )
LEFT JOIN ra_customer_trx_lines_all rl ON ( rt.customer_trx_id = rl.customer_trx_id
— AND oola.inventory_item_id = rl.inventory_item_id
AND to_number(rl.interface_line_attribute6) = oola.line_id
AND rl.line_type = ‘LINE’ )
LEFT JOIN ra_cust_trx_line_gl_dist_all rcg ON ( rcg.customer_trx_line_id = rl.customer_trx_line_id )
LEFT OUTER JOIN gl.gl_code_combinations cc ON cc.code_combination_id = rcg.code_combination_id
WHERE
1 = 1
AND ( oola.shipped_quantity IS NOT NULL
OR xxa_pkgutil.fntrxtype(rt.cust_trx_type_id) NOT IN ( ‘DOY Invoice’, ‘DOY Other’ )
–not in( 12027,20027)
)
AND rcg.amount IS NOT NULL
— AND cc.segment3 = ‘10100’
AND cc.segment3 BETWEEN ‘10100’ AND ‘10109’
AND oola.ship_from_org_id = :p52_inventory_org
AND ( rl.inventory_item_id IN (
SELECT DISTINCT
column_value
FROM
TABLE ( xxa_pkgutil.sflist(:p52_item, ‘:’) )
)
OR :p52_item IS NULL )
AND ooha.order_number IS NOT NULL
AND ( nvl(rt.sold_to_customer_id, rt.bill_to_customer_id) IN (
SELECT DISTINCT
column_value
FROM
TABLE ( xxa_pkgutil.sflist(:p52_customer, ‘:’) )
)
OR :p52_customer IS NULL )
AND trunc(rcg.gl_date) BETWEEN to_date(:p52_from_date, ‘DD-MON-YYYY’) AND to_date(:p52_to_date, ‘DD-MON-YYYY’)
UNION
SELECT
inventory_item_id,
description,
sold_to_customer_id,
to_number(:p52_inventory_org),
order_number,
trx_number,
unit_selling_price,
trx_date,
SUM(quantity_ordered) quantity_ordered,
– 1 * SUM(quantity_credited) return_quantity,
SUM(c_extended_amount) c_extended_amount,
– 1 * SUM(r_extended_amount) r_extended_amount,
to_number(interface_line_attribute6),
NULL reference_line_id,
cust_trx_type_id,
amount,
code_combination_id,
batch_source_id,
org_id,
gl_date,
purchase_order
FROM
(
SELECT
nvl(rt.sold_to_customer_id, rt.bill_to_customer_id) sold_to_customer_id,
rt.org_id,
rl.inventory_item_id,
CASE
WHEN rl.inventory_item_id IS NULL THEN
rl.description
ELSE
NULL
END description,
rt.trx_date,
NULL order_number,
rt.trx_number,
rl.unit_selling_price,
SUM(nvl(
CASE
WHEN rl.quantity_ordered < 0 THEN
– 1 * rl.quantity_ordered
ELSE
rl.quantity_ordered
END, 0)) quantity_ordered,
SUM(nvl(rl.quantity_credited, 0)) quantity_credited,
0 c_extended_amount,
SUM(nvl(rl.extended_amount, 0)) r_extended_amount,
to_number(rl.interface_line_attribute6) interface_line_attribute6,
rt.cust_trx_type_id,
rcg.amount,
rcg.code_combination_id,
rt.batch_source_id,
rcg.gl_date,
rt.purchase_order
FROM
ra_customer_trx_all rt,
ra_customer_trx_lines_all rl, ra_cust_trx_line_gl_dist_all rcg
LEFT OUTER JOIN gl.gl_code_combinations cc ON cc.code_combination_id = rcg.code_combination_id
WHERE
line_type = ‘LINE’
AND rt.customer_trx_id = rl.customer_trx_id
AND rt.trx_number IS NOT NULL
AND rcg.amount IS NOT NULL
AND rt.interface_header_attribute1 IS NULL
AND cc.segment3 BETWEEN ‘10100’ AND ‘10109’
AND rl.customer_trx_line_id = rcg.customer_trx_line_id
AND xxa_pkgutil.fntrxtype(rt.cust_trx_type_id) IN ( ‘DOY Credit Note’, ‘DOY Other CM’ )
AND rt.org_id = xxa_pkgutil.sfgetoperating_ut(:p52_inventory_org)
AND ( rl.inventory_item_id IN (
SELECT DISTINCT
column_value
FROM
TABLE ( xxa_pkgutil.sflist(:p52_item, ‘:’) )
)
OR :p52_item IS NULL )
AND ( nvl(rt.sold_to_customer_id, rt.bill_to_customer_id) IN (
SELECT DISTINCT
column_value
FROM
TABLE ( xxa_pkgutil.sflist(:p52_customer, ‘:’) )
)
OR :p52_customer IS NULL )
AND trunc(rcg.gl_date) BETWEEN to_date(:p52_from_date, ‘DD-MON-YYYY’) AND to_date(:p52_to_date,
‘DD-MON-YYYY’)
GROUP BY
nvl(rt.sold_to_customer_id, rt.bill_to_customer_id),
rt.org_id,
rl.inventory_item_id,
CASE
WHEN rl.inventory_item_id IS NULL THEN
rl.description
ELSE
NULL
END,
rt.trx_date,
rt.trx_number,
rl.unit_selling_price,
— rl.interface_line_attribute6
to_number(rl.interface_line_attribute6),
rt.cust_trx_type_id,
rcg.amount,
rcg.code_combination_id,
rt.batch_source_id,
rcg.gl_date,
rt.purchase_order
UNION
SELECT
nvl(rt.sold_to_customer_id, rt.bill_to_customer_id),
rt.org_id,
rl.inventory_item_id,
rl.description,
rt.trx_date,
NULL order_number,
rt.trx_number,
rl.unit_selling_price,
SUM(nvl(
CASE
WHEN rl.quantity_invoiced < 0 THEN
– 1 * rl.quantity_invoiced
ELSE
rl.quantity_invoiced
END, 0)) quantity_ordered,
0 quantity_credited,
SUM(nvl(rl.extended_amount, 0)) c_extended_amount,
0 r_extended_amount,
to_number(rl.interface_line_attribute6) interface_line_attribute6,
rt.cust_trx_type_id,
rcg.amount,
rcg.code_combination_id,
rt.batch_source_id,
rcg.gl_date,
rt.purchase_order
FROM
ra_customer_trx_all rt,
ra_customer_trx_lines_all rl, ra_cust_trx_line_gl_dist_all rcg
LEFT OUTER JOIN gl.gl_code_combinations cc ON cc.code_combination_id = rcg.code_combination_id
WHERE
line_type = ‘LINE’
AND rt.customer_trx_id = rl.customer_trx_id
AND rl.customer_trx_line_id = rcg.customer_trx_line_id
AND rt.interface_header_attribute1 IS NULL
AND xxa_pkgutil.fntrxtype(rt.cust_trx_type_id) IN ( ‘DOY Invoice’, ‘DOY Other’ )
AND rcg.amount IS NOT NULL
AND rt.trx_number IS NOT NULL
AND cc.segment3 BETWEEN ‘10100’ AND ‘10109’
AND rt.org_id = xxa_pkgutil.sfgetoperating_ut(:p52_inventory_org)
AND ( rl.inventory_item_id IN (
SELECT DISTINCT
column_value
FROM
TABLE ( xxa_pkgutil.sflist(:p52_item, ‘:’) )
)
OR :p52_item IS NULL )
AND trunc(rcg.gl_date) BETWEEN to_date(:p52_from_date, ‘DD-MON-YYYY’) AND to_date(:p52_to_date,
‘DD-MON-YYYY’)
AND ( nvl(rt.sold_to_customer_id, rt.bill_to_customer_id) IN (
SELECT DISTINCT
column_value
FROM
TABLE ( xxa_pkgutil.sflist(:p52_customer, ‘:’) )
)
OR :p52_customer IS NULL )
GROUP BY
nvl(rt.sold_to_customer_id, rt.bill_to_customer_id),
rt.org_id,
rl.inventory_item_id,
rl.description,
rt.trx_date,
rt.trx_number,
rl.unit_selling_price,
to_number(rl.interface_line_attribute6),
rt.cust_trx_type_id,
rcg.amount,
rcg.code_combination_id,
rt.batch_source_id,
rcg.gl_date,
rt.purchase_order
)
GROUP BY
inventory_item_id,
description,
sold_to_customer_id,
trx_date,
order_number,
trx_number,
unit_selling_price,
interface_line_attribute6,
cust_trx_type_id,
amount,
code_combination_id,
batch_source_id,
org_id,
gl_date,
purchase_order
)
WHERE
1 = 1
GROUP BY
inventory_item_id,
description,
sold_to_org_id,
ship_from_org_id,
order_number,
trx_number,
trx_date,
unit_price,
line_id,
reference_line_id,
cust_trx_type_id,
amount,
code_combination_id,
batch_source_id,
org_id,
gl_date,
purchase_order
— variance starts
UNION
SELECT DISTINCT
mmt.inventory_item_id,
NULL description,
(
SELECT
ci.customer_id
FROM
apps.mtl_customer_item_xrefs cix
JOIN apps.mtl_customer_items ci ON ci.customer_item_id = cix.customer_item_id
WHERE
cix.inventory_item_id = mmt.inventory_item_id
),
mmt.organization_id,
NULL,
NULL,
mmt.transaction_date,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
mmt.transaction_date,
NULL purchase_order
FROM
mtl_material_transactions mmt,
mtl_transaction_accounts mta,
gl_code_combinations gcc
WHERE
mmt.organization_id = :p52_inventory_org
AND mmt.transaction_id = mta.transaction_id
AND gcc.code_combination_id = mta.reference_account
AND gcc.segment3 = ‘12220’
AND gcc.segment4 = ‘1105’
AND trunc(mmt.transaction_date) BETWEEN to_date(:p52_from_date, ‘DD-MON-YYYY’) AND to_date(:p52_to_date, ‘DD-MON-YYYY’)
AND NOT EXISTS (
SELECT
1
FROM
ra_customer_trx_all rt,
ra_customer_trx_lines_all rl, ra_cust_trx_line_gl_dist_all rcg
LEFT OUTER JOIN gl.gl_code_combinations cc ON cc.code_combination_id = rcg.code_combination_id
WHERE
line_type = ‘LINE’
AND rt.customer_trx_id = rl.customer_trx_id
AND rl.customer_trx_line_id = rcg.customer_trx_line_id
AND rt.trx_number IS NOT NULL
AND rt.org_id = xxa_pkgutil.sfgetoperating_ut(:p52_inventory_org)
AND mmt.inventory_item_id = rl.inventory_item_id
AND trunc(rcg.gl_date) BETWEEN to_date(:p52_from_date, ‘DD-MON-YYYY’) AND to_date(:p52_to_date, ‘DD-MON-YYYY’)
) — variance ends
) x
)
GROUP BY
sold_to_org_id,
ship_from_org_id,
inventory_item_id,
doc_currency,
description,
cogs_variance
) vw2
LEFT JOIN mtl_system_items_b msi ON vw2.inventory_item_id = msi.inventory_item_id
AND vw2.ship_from_org_id = msi.organization_id (+)
LEFT JOIN hz_cust_accounts hca ON hca.cust_account_id = vw2.sold_to_org_id
LEFT JOIN hz_parties hp ON hp.party_id = hca.party_id
WHERE
1 = 1
AND ( hp.attribute7 IN (
SELECT DISTINCT
column_value
FROM
TABLE ( xxa_pkgutil.sflist(:p52_customer_gka, ‘:’) )
)
OR :p52_customer_gka IS NULL )
ORDER BY
vw2.sold_to_org_id,
vw2.inventory_item_id

5. Screen Shot

Output:

This will be the output that we can generated using this document.

Recent Posts

Start typing and press Enter to search