ORACLE MASTERMINDS

Monday, 25 February 2019

API to Update the category description

DECLARE
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_category_id NUMBER;
l_description VARCHAR2(80);
g_prem VARCHAR2(10);
BEGIN
select mcb.CATEGORY_ID into l_category_id
from mtl_categories_b mcb
where mcb.SEGMENT1='BLACK'
and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
from mtl_category_sets_b mcs_b
where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
from mtl_category_sets_tl mcs_tl
where CATEGORY_SET_NAME ='INV_COLORS_SET'));

l_description := 'New Black Color';

INV_ITEM_CATEGORY_PUB.Update_Category_Description (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_id => l_category_id,
p_description => l_description);

IF l_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Update of Item Category Description is Successful : '||l_category_id);
ELSE
DBMS_OUTPUT.put_line ('Update of Item Category Description Failed with the error :'||l_error_code);
ROLLBACK;
END IF;
END ;

API to Delete Valid Category Set

DECLARE
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_category_set_id NUMBER;
l_category_id NUMBER;
g_prem varchar2(10);
BEGIN
select mcs_tl.CATEGORY_SET_ID into l_category_set_id
from mtl_category_sets_tl mcs_tl
where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

select mcb.CATEGORY_ID into l_category_id
from mtl_categories_b mcb
where mcb.SEGMENT1='RED'
and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
from mtl_category_sets_b mcs_b
where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
from mtl_category_sets_tl mcs_tl
where CATEGORY_SET_NAME ='INV_COLORS_SET'));

INV_ITEM_CATEGORY_PUB.Delete_Valid_Category (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_set_id => l_category_set_id,
p_category_id => l_category_id);

IF l_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Delete Valid Category is Successful : '||l_category_id);
ELSE
DBMS_OUTPUT.put_line ('Delete Valid Category Failed with the error :'||l_error_code);
ROLLBACK;
END IF;
END ;

API to Assign Item to Inventory

DECLARE
g_user_id fnd_user.user_id%TYPE :=NULL;
l_appl_id fnd_application.application_id%TYPE;
l_resp_id fnd_responsibility_tl.responsibility_id%TYPE;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := fnd_api.g_false;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
x_message_list error_handler.error_tbl_type;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
g_prem varchar2(10);
BEGIN
SELECT fa.application_id
INTO l_appl_id
FROM fnd_application fa
WHERE fa.application_short_name = 'INV';

SELECT fr.responsibility_id
INTO l_resp_id
FROM fnd_application fa, fnd_responsibility_tl fr
WHERE fa.application_short_name = 'INV'
AND fa.application_id = fr.application_id
AND UPPER (fr.responsibility_name) = 'INVENTORY';

fnd_global.apps_initialize (g_user_id, l_resp_id, l_appl_id);

EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(
P_API_VERSION => l_api_version
, P_INIT_MSG_LIST => l_init_msg_list
, P_COMMIT => l_commit
, P_INVENTORY_ITEM_ID => 209957
, p_item_number => 'CM151403'
, p_organization_id => 204
, P_ORGANIZATION_CODE => 'V1'
, P_PRIMARY_UOM_CODE => 'EA'
, X_RETURN_STATUS => x_return_status
, X_MSG_COUNT => x_msg_count
);
DBMS_OUTPUT.PUT_LINE('Status: '||x_return_status);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('Error Messages :');
Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
FOR j IN 1..x_message_list.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(x_message_list(j).message_text);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception Occured :');
DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
END;

API Create a valid category set

DECLARE
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_category_set_id NUMBER;
l_category_id NUMBER;
g_prem varchar2(10);
BEGIN
select mcs_tl.CATEGORY_SET_ID into l_category_set_id
from mtl_category_sets_tl mcs_tl
where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

select mcb.CATEGORY_ID into l_category_id
from mtl_categories_b mcb
where mcb.SEGMENT1='RED'
and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
from mtl_category_sets_b mcs_b
where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
from mtl_category_sets_tl mcs_tl
where CATEGORY_SET_NAME ='INV_COLORS_SET'));

INV_ITEM_CATEGORY_PUB.Create_Valid_Category (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_set_id => l_category_set_id,
p_category_id => l_category_id,
p_parent_category_id => NULL );

IF l_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Create Valid Category is Successful : '||l_category_id);
ELSE
DBMS_OUTPUT.put_line ('Create Valid Category Failed with the error :'||l_error_code);
ROLLBACK;
END IF;
END ;

All approved requisition report for the month including PO

SELECT R.SEGMENT1 "Req number",
Rl.Line_Num "Req line",
rl.suggested_vendor_product_code supplier_item,
(SELECT DISTINCT Description
FROM Apps.Mtl_Categories
WHERE Category_Id=Rl.Category_Id
) "Req Line Category Description",
(SELECT DISTINCT segment1
    ||'.'
    ||segment2
FROM Apps.Mtl_Categories
WHERE Category_Id=rl.category_id
) "Requisition Line Category" ,
TO_CHAR(R.APPROVED_DATE,'dd-MON-RR HH24:MI:SS') "Req Approved Date",
P.SEGMENT1 "PO Number",
p.revision_num "Revision number",
PL.LINE_NUM "PO Line",
TO_CHAR(P.CREATION_DATE,'DD-MON-RRRR HH24:MI:SS') "PO Created Date",
pagen.agent_name "PO buyer",
p.attribute10 "Contract Type",
p.org_id org_id,
(SELECT NVL(short_code,name)
FROM apps.hr_operating_units
WHERE organization_id = p.org_id
) org_code,
aps.vendor_name
FROM APPS.PO_HEADERS_ALL P,
apps.po_lines_all pl,
APPS.PO_DISTRIBUTIONS_ALL D,
apps.po_agents_v pagen,
apps.po_req_distributions_all rd,
apps.po_requisition_lines_all rl,
apps.po_requisition_headers_all r,
apps.ap_suppliers aps
WHERE P.PO_HEADER_ID         = D.PO_HEADER_ID
AND P.PO_HEADER_ID           = PL.PO_HEADER_ID
AND PL.PO_LINE_ID            = D.PO_LINE_ID
AND pagen.agent_id           = p.agent_id
and aps.vendor_id            = p.vendor_id
AND d.req_distribution_id    = rd.distribution_id
AND rd.requisition_line_id   = rl.requisition_line_id
AND RL.REQUISITION_HEADER_ID = R.REQUISITION_HEADER_ID
AND P.Org_Id                IN (82,83, 367, 370, 627, 628, 629, 388, 630, 395, 376, 396, 382,393,378 )
AND TRUNC(R.APPROVED_DATE) >='01-NOV-2018'
AND TRUNC(R.APPROVED_DATE) <='30-NOV-2018'
/*ORDER BY org_id,
    TO_CHAR(R.APPROVED_DATE,'DD-MON-RRRR HH24:MI:SS')*/
      UNION
    SELECT R.SEGMENT1 "Req number",
Rl.Line_Num "Req line",
rl.suggested_vendor_product_code supplier_item,
(SELECT DISTINCT Description
FROM Apps.Mtl_Categories
WHERE Category_Id=Rl.Category_Id
) "Req Line Category Description",
(SELECT DISTINCT segment1
    ||'.'
    ||segment2
FROM Apps.Mtl_Categories
WHERE Category_Id=rl.category_id
) "Requisition Line Category" ,
TO_CHAR(R.APPROVED_DATE,'dd-MON-RR HH24:MI:SS') "Req Approved Date",
NULL "PO Number",
NULL "PO Revision number",
NULL "PO Line",
NULL "PO Created Date",
NULL "PO buyer",
NULL "Contract Type",
r.org_id org_id,
(SELECT NVL(short_code,name)
FROM apps.hr_operating_units
WHERE organization_id = r.org_id
) org_code,
aps.vendor_name
FROM apps.po_requisition_headers_all r,
      apps.po_requisition_lines_all rl,
      apps.po_req_distributions_all rd
    , apps.ap_suppliers aps
WHERE 1=1
AND RL.REQUISITION_HEADER_ID = R.REQUISITION_HEADER_ID
AND rd.requisition_line_id   = rl.requisition_line_id
and rl.vendor_id = aps.vendor_id
AND Not exists ( select req_distribution_id
     from apps.po_distributions_all
     where req_distribution_id = rd.distribution_id)
AND r.Org_Id                IN (82,83, 367, 370, 627, 628, 629, 388, 630, 395, 376, 396, 382,393,378 )
AND TRUNC(R.APPROVED_DATE) >='01-NOV-2018'
AND TRUNC(R.APPROVED_DATE) <='30-NOV-2018'
/*ORDER BY --org_id,
    TO_CHAR(R.APPROVED_DATE,'DD-MON-RRRR HH24:MI:SS')
    */

Sales Revenue Straight Line Calculation Report

<?xml version = '1.0' encoding = 'UTF-8'?>









<dataTemplate name="NA_CUSTAGREE" defaultPackage="NA_CUST_AGRE_RPT_PKG" version="1.0">
<properties>
<property name="debug_mode" value="off" />
</properties>
<parameters>
<parameter name="P_ORG_ID" dataType="number" include_in_output="true"/>
<parameter name="P_CUSTID_FROM" dataType="number" include_in_output="true"/>
<parameter name="P_AGREEMENT_FROM" dataType="character" include_in_output="true"/>
<parameter name="P_AGREEMENT_TO" dataType="character" include_in_output="true"/>
<parameter name="P_PERIOD_FROM" dataType="character" include_in_output="true"/>
<parameter name="P_PERIOD_TO" dataType="character" include_in_output="true"/>
</parameters>
<dataQuery>
<sqlStatement name="Q_MAINQRY">
<![CDATA[
select
hca.account_number customer_number,
inv_hp.party_name customer_name,
ios.agreement_number,
shl.line_number,
shl.ordered_item,
msi.description item_desc,
shl.order_quantity_uom uom,
shl.attribute5 Quantity,
shl.attribute6 price,
inv_su.location Ship_to_loc,
ios.line_group,
gcc.concatenated_segments sales_account,
ios.monthly_date,
ios.monthly_bill_amt,
ios.revenue_recogn,
ios.total_value
from apps.oe_blanket_headers_all sha,
apps.oe_blanket_lines_all shl,
apps.hz_cust_site_uses_all inv_su,
apps.hz_parties inv_hp,
apps.hz_party_sites inv_ps,
apps.hz_cust_accounts hca,
apps.hz_cust_acct_sites_all inv_cas,
apps.mtl_system_items_b msi,
apps.gl_code_combinations_kfv gcc,
iron.irm_om_salagree_stig_tmp ios
where sha.header_id = shl.header_id
and sha.org_id = shl.org_id
and sha.org_id = :P_ORG_ID
and shl.attribute4 = 'S'
and sha.cancelled_flag is null
and shl.cancelled_flag is null
and shl.invoice_to_org_id = inv_su.site_use_id (+)
AND inv_su.cust_acct_site_id = inv_cas.cust_acct_site_id (+)
AND inv_cas.party_site_id = inv_ps.party_site_id (+)
AND inv_hp.party_id = inv_ps.party_id
AND INV_HP.party_id = hca.party_id(+)
and shl.inventory_item_id = msi.inventory_item_id
and shl.ship_from_org_id = msi.organization_id
and msi.sales_account = gcc.code_combination_id
and ios.cust_account_id = hca.cust_account_id
and ios.line_id = shl.line_id
and ios.line_number = shl.line_number
and ios.agreement_number = sha.order_number
order by ios.agreement_number, ios.line_group, to_date(ios.monthly_date, 'DD-MON-YYYY') ASC
]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReport" source="IRON_NA_CUST_AGRE_RPT_PKG.BeforeReport(:P_ORG_ID,:P_CUSTID_FROM,:P_AGREEMENT_FROM,:P_AGREEMENT_TO,:P_PERIOD_FROM,:P_PERIOD_TO)"/>
<dataStructure>
<group name="G_AGREEMENT" source="Q_MAINQRY">
<element name="CUSTOMER_NUMBER" value="CUSTOMER_NUMBER"/>
<element name="CUSTOMER_NAME" value="CUSTOMER_NAME"/>
<element name="AGREEMENT_NUMBER" value="AGREEMENT_NUMBER"/>
<element name="LINE_GROUP" value="LINE_GROUP"/>
<group name="G_AGRMNT_LINE" source="Q_MAINQRY">
<element name="LINE_NUMBER" value="LINE_NUMBER"/>
<element name="ORDERED_ITEM" value="ORDERED_ITEM"/>
<element name="SALES_ACCOUNT" value="SALES_ACCOUNT"/>
<element name="MONTH" value="MONTHLY_DATE"/>
<element name="MONTHLY_BILL_AMOUNT" value="MONTHLY_BILL_AMT"/>
<element name="REVENUE_RECOGNIZED" value="REVENUE_RECOGN"/>
<element name="TOTAL_VALUE" value="TOTAL_VALUE"/>
<element name="ORDERED_ITEM" value="ORDERED_ITEM"/>
<element name="ITEM_DESC" value="ITEM_DESC"/>
<element name="UOM" value="UOM"/>
<element name="QUANTITY" value="QUANTITY"/>
<element name="PRICE" value="PRICE"/>
<element name="SHIP_TO_LOC" value="SHIP_TO_LOC"/>
</group>
</group>
</dataStructure>
<dataTrigger name="AfterReport" source="NA_CUST_AGRE_RPT_PKG.AfterReport"/>
</dataTemplate>