ORACLE MASTERMINDS: February 2019

Monday 25 February 2019

AR Invoice PDF email (Alternative of BPA Process)

<?xml version="1.0"?>
<dataTemplate name="XX__AR_INVPRINT" description="TW Invoice Printout for United States" defaultPackage="XX_AR_INVPRINT_PKG" version="1.0">
<parameters>
<parameter name="P_Report_Level" dataType = "character"></parameter>
<parameter name="P_Report_Context" dataType = "character"></parameter>
<parameter name="P_TRANSACTION_SOURCE" dataType = "character"></parameter>
<parameter name="P_TRANSACTION_TYPE" dataType = "number"></parameter>
<parameter name="P_TRANSACTION_CLASS" dataType = "character"></parameter>
<parameter name="P_DATE_FROM" dataType = "character"></parameter>
<parameter name="P_DATE_TO" dataType = "character"></parameter>
<parameter name="P_TRANS_NUMBER_LOW" dataType = "character"></parameter>
<parameter name="P_TRANS_NUMBER_HIGH" dataType = "character"></parameter>
<parameter name="P_CUST_NAME" dataType = "character"></parameter>
<parameter name="P_PERIOD" dataType = "character"></parameter>
</parameters>
<dataQuery>
<sqlStatement name="Q_HEADER">
<![CDATA[
SELECT DISTINCT TRANSACTION_ID,
NVL (fnd_profile.VALUE ('IMDC:BPA_INVOICE_WEB_LINK'), 'For Online Payments : www.xx.com') PAYMENT_WEB_ADDRESS,
bill_to_customer_name CUSTOMER_NAME,
PURCHASE_ORDER_NUMBER,
case when XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') is null then
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11||' - '||' Please Print and Send '
else
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11
end subject,
FORMATTED_BILL_TO_ADDRESS,
FORMATTED_REMIT_TO_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_EMAIL'), 'Email : xx@xx.com') REMIT_EMAIL_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_PHONE'), ' ') REMIT_PHONE_NUMBER,
TRANSACTION_NUMBER,
TRANSACTION_DATE,
ATTRIBUTE11 PERIOD,
ACCOUNT_NUMBER,
BILL_TO_LOCATION,
to_char(fnd_date.canonical_to_date(attribute10),'DD-MON-YYYY') COMMENCEMENT_DATE,
INVOICE_CURRENCY_CODE,
TERM_NAME,
term_due_date_from_ps TERM_DATE,
CASE WHEN :P_TRANS_NUMBER_LOW IS NOT NULL AND :P_TRANS_NUMBER_HIGH IS NOT NULL THEN
'xx@xx.com'
ELSE
NVL(XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI'),'xx@xx.com') END AS EMAIL,
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') CUSTEMAIL,
CASE WHEN :P_TRANS_NUMBER_LOW IS NULL AND :P_TRANS_NUMBER_HIGH IS NULL THEN
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'SEC') ELSE NULL END AS SEC_EMAIL,
CUSTOMER_CONTACT_NAME ,
CUSTOMER_PHONE,
LINE_AMOUNT,
TAX_AMOUNT,
FREIGHT_AMOUNT,
TOTAL_AMOUNT,
PAYMENTS_AND_CREDITS,
FINANCE_CHARGES,
TO_CHAR (OUTSTANDING_BALANCE, fnd_currency.get_format_mask ( invoice_currency_code, 40 )) OUTSTANDING_BALANCE,
CURRENT_DATE,
CT_REFERENCE
FROM XXAR_CUSTOMER_TRX_HEADER
WHERE org_id = 11
AND CUST_TRX_TYPE_ID IN (123)
AND TRANSACTION_ID IN (SELECT CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_ALL
WHERE org_id = 11
AND CUST_TRX_TYPE_ID IN (123)
AND TRX_NUMBER BETWEEN :P_TRANS_NUMBER_LOW
AND :P_TRANS_NUMBER_HIGH)
AND :P_TRANS_NUMBER_LOW IS NOT NULL
AND :P_TRANS_NUMBER_HIGH IS NOT NULL
AND :P_PERIOD IS NULL
UNION
SELECT DISTINCT TRANSACTION_ID,
NVL (fnd_profile.VALUE ('IMDC:BPA_INVOICE_WEB_LINK'), 'For Online Payments : www.xx.com') PAYMENT_WEB_ADDRESS,
bill_to_customer_name CUSTOMER_NAME,
PURCHASE_ORDER_NUMBER,
case when XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') is null then
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11||' - '||' Please Print and Send '
else
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11
end subject,
FORMATTED_BILL_TO_ADDRESS,
FORMATTED_REMIT_TO_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_EMAIL'), 'Email : xx@xx.com') REMIT_EMAIL_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_PHONE'), ' ') REMIT_PHONE_NUMBER,
TRANSACTION_NUMBER,
TRANSACTION_DATE,
ATTRIBUTE11 PERIOD,
ACCOUNT_NUMBER,
BILL_TO_LOCATION,
to_char(fnd_date.canonical_to_date(attribute10),'DD-MON-YYYY') COMMENCEMENT_DATE,
INVOICE_CURRENCY_CODE,
TERM_NAME,
term_due_date_from_ps TERM_DATE,
CASE WHEN :P_TRANS_NUMBER_LOW IS NOT NULL AND :P_TRANS_NUMBER_HIGH IS NOT NULL THEN
'xx@xx.com'
ELSE
NVL(XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI'),'xx@xx.com') END AS EMAIL,
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') CUSTEMAIL,
CASE WHEN :P_TRANS_NUMBER_LOW IS NULL AND :P_TRANS_NUMBER_HIGH IS NULL THEN
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'SEC') ELSE NULL END AS SEC_EMAIL,
CUSTOMER_CONTACT_NAME ,
CUSTOMER_PHONE,
LINE_AMOUNT,
TAX_AMOUNT,
FREIGHT_AMOUNT,
TOTAL_AMOUNT,
PAYMENTS_AND_CREDITS,
FINANCE_CHARGES,
TO_CHAR (OUTSTANDING_BALANCE, fnd_currency.get_format_mask ( invoice_currency_code, 40 )) OUTSTANDING_BALANCE,
CURRENT_DATE,
CT_REFERENCE
FROM XXAR_CUSTOMER_TRX_HEADER , gl_periods gp
WHERE org_id = 11
AND CUST_TRX_TYPE_ID IN (123)
and GP.PERIOD_SET_NAME = 'XX_CALENDAR'
AND GP.PERIOD_NAME = :P_PERIOD
AND TRX_DATE BETWEEN GP.START_DATE AND GP.END_DATE
AND PRINTING_LAST_PRINTED IS NULL
AND :P_PERIOD IS NOT NULL
AND :P_TRANS_NUMBER_LOW IS NULL
AND :P_TRANS_NUMBER_HIGH IS NULL
UNION
SELECT DISTINCT TRANSACTION_ID,
NVL (fnd_profile.VALUE ('IMDC:BPA_INVOICE_WEB_LINK'), 'For Online Payments : www.xx.com') PAYMENT_WEB_ADDRESS,
bill_to_customer_name CUSTOMER_NAME,
PURCHASE_ORDER_NUMBER,
case when XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') is null then
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11||' - '||' Please Print and Send '
else
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11
end subject,
FORMATTED_BILL_TO_ADDRESS,
FORMATTED_REMIT_TO_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_EMAIL'), 'Email : xx@xx.com') REMIT_EMAIL_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_PHONE'), ' ') REMIT_PHONE_NUMBER,
TRANSACTION_NUMBER,
TRANSACTION_DATE,
ATTRIBUTE11 PERIOD,
ACCOUNT_NUMBER,
BILL_TO_LOCATION,
to_char(fnd_date.canonical_to_date(attribute10),'DD-MON-YYYY') COMMENCEMENT_DATE,
INVOICE_CURRENCY_CODE,
TERM_NAME,
term_due_date_from_ps TERM_DATE,
CASE WHEN :P_TRANS_NUMBER_LOW IS NOT NULL AND :P_TRANS_NUMBER_HIGH IS NOT NULL THEN
'xx@xx.com'
ELSE
NVL(XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI'),'xx@xx.com') END AS EMAIL,
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') CUSTEMAIL,
CASE WHEN :P_TRANS_NUMBER_LOW IS NULL AND :P_TRANS_NUMBER_HIGH IS NULL THEN
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'SEC') ELSE NULL END AS SEC_EMAIL,
CUSTOMER_CONTACT_NAME ,
CUSTOMER_PHONE,
LINE_AMOUNT,
TAX_AMOUNT,
FREIGHT_AMOUNT,
TOTAL_AMOUNT,
PAYMENTS_AND_CREDITS,
FINANCE_CHARGES,
TO_CHAR (OUTSTANDING_BALANCE, fnd_currency.get_format_mask ( invoice_currency_code, 40 )) OUTSTANDING_BALANCE,
CURRENT_DATE,
CT_REFERENCE
FROM XXAR_CUSTOMER_TRX_HEADER , gl_periods gp
WHERE org_id = 11
AND CUST_TRX_TYPE_ID IN (123)
AND TRANSACTION_ID IN (SELECT CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_ALL
WHERE org_id = 11
AND CUST_TRX_TYPE_ID IN (123)
AND TRX_NUMBER BETWEEN :P_TRANS_NUMBER_LOW
AND :P_TRANS_NUMBER_HIGH)
and GP.PERIOD_SET_NAME = 'XX_CALENDAR'
AND GP.PERIOD_NAME = :P_PERIOD
AND TRX_DATE BETWEEN GP.START_DATE AND GP.END_DATE
AND :P_PERIOD IS NOT NULL
AND :P_TRANS_NUMBER_LOW IS NOT NULL
AND :P_TRANS_NUMBER_HIGH IS NOT NULL
]]>
</sqlStatement>
<sqlStatement name="Q_TRX_LINES">
<![CDATA[
SELECT DESCRIPTION, QUANTITY,UNIT_OF_MEASURE_NAME,START_DATE,UNIT_PRICE,EXTENDED_AMOUNT,SALES_ORDER,
UOM_CODE,TRX_NUMBER,DATA_CENTER
FROM XXAR_CUSTOMER_TRX_LINE
WHERE CUSTOMER_TRX_ID = :TRANSACTION_ID
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_HEADER" source="Q_HEADER">
<element name="PAYMENT_WEB_ADDRESS" value="PAYMENT_WEB_ADDRESS"/>
<element name="CUSTOMER_NAME" value="CUSTOMER_NAME"/>
<element name="PURCHASE_ORDER_NUMBER" value="PURCHASE_ORDER_NUMBER"/>
<element name="SUBJECT" value="SUBJECT"/>
<element name="FORMATTED_BILL_TO_ADDRESS" value="FORMATTED_BILL_TO_ADDRESS"/>
<element name="FORMATTED_REMIT_TO_ADDRESS" value="FORMATTED_REMIT_TO_ADDRESS"/>
<element name="REMIT_EMAIL_ADDRESS" value="REMIT_EMAIL_ADDRESS"/>
<element name="REMIT_PHONE_NUMBER" value="REMIT_PHONE_NUMBER"/>
<element name="TRANSACTION_NUMBER" value="TRANSACTION_NUMBER"/>
<element name="TRANSACTION_DATE" value="TRANSACTION_DATE"/>
<element name="PERIOD" value="PERIOD"/>
<element name="ACCOUNT_NUMBER" value="ACCOUNT_NUMBER"/>
<element name="BILL_TO_LOCATION" value="BILL_TO_LOCATION"/>
<element name="COMMENCEMENT_DATE" value="COMMENCEMENT_DATE"/>
<element name="INVOICE_CURRENCY_CODE" value="INVOICE_CURRENCY_CODE"/>
<element name="TERM_NAME" value="TERM_NAME"/>
<element name="TERM_DATE" value="TERM_DATE"/>
<element name="EMAIL" value="EMAIL"/>
<element name="CUSTEMAIL" value="CUSTEMAIL"/>
<element name="SEC_EMAIL" value="SEC_EMAIL"/>
<element name="CUSTOMER_CONTACT_NAME" value="CUSTOMER_CONTACT_NAME"/>
<element name="CUSTOMER_PHONE" value="CUSTOMER_PHONE"/>
<element name="LINE_AMOUNT" value="LINE_AMOUNT"/>
<element name="TAX_AMOUNT" value="TAX_AMOUNT"/>
<element name="FREIGHT_AMOUNT" value="FREIGHT_AMOUNT"/>
<element name="TOTAL_AMOUNT" value="TOTAL_AMOUNT"/>
<element name="PAYMENTS_AND_CREDITS" value="PAYMENTS_AND_CREDITS"/>
<element name="FINANCE_CHARGES" value="FINANCE_CHARGES"/>
<element name="OUTSTANDING_BALANCE" value="OUTSTANDING_BALANCE"/>
<element name="CURRENT_DATE" value="CURRENT_DATE"/>
<element name="CT_REFERENCE" value="CT_REFERENCE"/>
<group name="G_LINES" source="Q_TRX_LINES">
<element name="DESCRIPTION" value="DESCRIPTION"/>
<element name="QUANTITY" value="QUANTITY"/>
<element name="UNIT_OF_MEASURE_NAME" value="UNIT_OF_MEASURE_NAME"/>
<element name="START_DATE" value="START_DATE"/>
<element name="UNIT_PRICE" value="UNIT_PRICE"/>
<element name="EXTENDED_AMOUNT" value="EXTENDED_AMOUNT"/>
<element name="DATA_CENTER" value="DATA_CENTER"/>
</group>
</group>
</dataStructure>
<dataTrigger name="afterReportTrigger" source="XX_AR_INVPRINT_PKG.AfterReport"/>
</dataTemplate>

Program for Return to Vendor in Oracle purchasing

Return to Vendor of Purchase Order Receipts Script: Oracle EBS R12
Return to Vendor is done in two steps: "Return to Receiving" and then "Return to Vendor".
Follow below steps to return the received goods to vendor.

Step:1 Run the following script to find the necessary information to be inserted in RCV_TRANSACTIONS_INTERFACE table.

SELECT rsh.receipt_num ,
ph.segment1 po_number,
rt.transaction_id ,
rt.transaction_type ,
rt.transaction_date ,
rt.quantity ,
rt.unit_of_measure ,
rt.shipment_header_id ,
rt.shipment_line_id ,
rt.source_document_code ,
rt.destination_type_code ,
rt.employee_id ,
rt.parent_transaction_id ,
rt.po_header_id ,
rt.po_line_id ,
pl.line_num ,
pl.item_id ,
pl.unit_price ,
rt.po_line_location_id ,
rt.po_distribution_id ,
rt.routing_header_id,
rt.routing_step_id ,
rt.deliver_to_person_id ,
rt.deliver_to_location_id ,
rt.vendor_id ,
rt.vendor_site_id ,
rt.organization_id ,
rt.subinventory ,
rt.locator_id ,
rt.location_id,
rsh.ship_to_org_id
FROM apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.po_headers_all ph,
apps.po_lines_all pl
WHERE rsh.receipt_num = '4298'
AND ph.segment1 = '6026'
AND ph.po_header_id = pl.po_header_id
AND rt.po_header_id = ph.po_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND rt.po_line_id =pl.po_line_id
AND rt.destination_type_code='INVENTORY'
AND rt.transaction_type='DELIVER';

Step:2 Return from Deliver to Receiving i.e Return to Receiving

INSERT INTO apps.rcv_transactions_interface
(interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
quantity,
unit_of_measure,
item_id,
employee_id,
shipment_header_id,
shipment_line_id,
receipt_source_code,
vendor_id,
from_organization_id,
from_subinventory,
from_locator_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
destination_type_code,
deliver_to_person_id,
location_id,
deliver_to_location_id,
validation_flag
)
VALUES
(apps.rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
apps.rcv_interface_groups_s.nextval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
1318, --LAST_UPDATE_BY
SYSDATE, --CREATION_DATE
1318, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO RECEIVING', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
25, --QUANTITY
'METRICTON', --UNIT_OF_MEASURE
208955, --ITEM_ID
25, --EMPLOYEE_ID
5048067, --SHIPMENT_HEADER_ID
5035611, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
557, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
5090687, --TRANSACTION_ID
165864, --PO_HEADER_ID
232886, --PO_LINE_ID
323424, --PO_LINE_LOCATION_ID
329884, --PO_DISTRIBUTION_ID
'INVENTORY', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
NULL, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --Validation_flag

);
commit;

Step:3 Submit the Receiving Transaction Processor concurrent program

SET serveroutput ON
DECLARE
v_request_id NUMBER;
BEGIN
apps.mo_global.init ('PO');
apps.mo_global.set_policy_context ('S',204);
apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );
--------CALLING STANDARD RECEIVING TRANSACTION PROCESSOR ---------------------------------

v_request_id := apps.fnd_request.submit_request ( application => 'PO',
PROGRAM => 'RVCTP',
argument1 => 'BATCH',
argument2 => apps.rcv_interface_groups_s.currval,
argument3 => 204);
commit;
dbms_output.put_line('Request Id'||v_request_id);
END;

Step:4 Run the script to check data is inserted in proper way in rcv_transactions.

SELECT * FROM apps.rcv_transactions WHERE po_header_id=58641;

Step:5 Once the goods are transferred to receiving destination return to vendor.

INSERT INTO apps.rcv_transactions_interface
(interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
quantity,
unit_of_measure,
item_id,
employee_id,
shipment_header_id,
shipment_line_id,
receipt_source_code,
vendor_id,
from_organization_id,
from_subinventory,
from_locator_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
destination_type_code,
deliver_to_person_id,
location_id,
deliver_to_location_id,
validation_flag
)
values
(apps.rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
apps.rcv_interface_groups_s.nextval, --GROUP_ID
sysdate, --LAST_UPDATE_DATE
1318, --LAST_UPDATE_BY
sysdate, --CREATION_DATE
1318, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO VENDOR', --TRANSACTION_TYPE
sysdate, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
25, --QUANTITY
'METRICTON', --UNIT_OF_MEASURE
208955, --ITEM_ID
25, --EMPLOYEE_ID
5048067, --SHIPMENT_HEADER_ID
5035611, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
557, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
5090686, --PARENT_TRANSACTION_ID
165864, --PO_HEADER_ID
232886, --PO_LINE_ID
323424, --PO_LINE_LOCATION_ID
329884, --PO_DISTRIBUTION_ID
'RECEIVING', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
null, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --Validation_flag
);
commit;

Step:6 Submit the Receiving Transaction Processor concurrent program

SET serveroutput ON
DECLARE
v_request_id NUMBER;
BEGIN
apps.mo_global.init ('PO');
apps.mo_global.set_policy_context ('S',204);
apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );
--------CALLING STANDARD RECEIVING TRANSACTION PROCESSOR ---------------------------------

v_request_id := apps.fnd_request.submit_request ( application => 'PO',
PROGRAM => 'RVCTP',
argument1 => 'BATCH',
argument2 => apps.rcv_interface_groups_s.currval,
argument3 => 204);
commit;
dbms_output.put_line('Request Id'||v_request_id);
END;

Program to create receipts for approved Purchase order

DECLARE
ln_user_id NUMBER:=1318;
ln_po_header_id NUMBER;
ln_vendor_id NUMBER;
lv_segment1 VARCHAR2(20);
ln_org_id NUMBER;
ln_line_num NUMBER;
ln_parent_txn_id NUMBER;

CURSOR po_line IS
SELECT pl.item_id,
pl.po_line_id,
pl.line_num,
pd.quantity_ordered quantity,
pd.po_distribution_id,
pl.unit_meas_lookup_code,
mp.organization_code,
pll.line_location_id,
pll.closed_code,
pll.quantity_received,
pll.cancel_flag,
pll.shipment_num
FROM po_lines_all pl,
po_line_locations_all pll,
po_distributions_all pd,
mtl_parameters mp
WHERE pl.po_header_id = ln_po_header_id
AND pl.po_line_id = pll.po_line_id
AND pd.line_location_id = pll.line_location_id
AND pd.po_line_id = pl.po_line_id
AND pll.ship_to_organization_id = mp.organization_id;

BEGIN

ln_user_id := 1318;

SELECT po_header_id,
vendor_id,
segment1,
org_id
INTO ln_po_header_id,
ln_vendor_id,
lv_segment1,
ln_org_id
FROM po_headers_all
WHERE segment1 ='6050'
AND org_id = 204;

INSERT INTO rcv_headers_interface
(header_interface_id,
group_id,
processing_status_code,
receipt_source_code,
transaction_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
vendor_id,
expected_receipt_date,
validation_flag,
org_id)
SELECT rcv_headers_interface_s.nextval,
rcv_interface_groups_s.nextval,
'PENDING',
'VENDOR',
'NEW',
sysdate,
ln_user_id,
sysdate,
ln_user_id,
0,
ln_vendor_id,
sysdate,
'Y',
ln_org_id
FROM dual;

FOR cur_po_line IN po_line
LOOP
IF cur_po_line.closed_code IN ('APPROVED', 'OPEN')
AND cur_po_line.quantity_received < cur_po_line.quantity
AND NVL(cur_po_line.cancel_flag,'N') = 'N'
THEN
INSERT INTO rcv_transactions_interface
(interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
po_header_id,
po_line_id,
item_id,
quantity,
unit_of_measure,
po_line_location_id,
po_distribution_id,
destination_type_code,
destination_context,
auto_transact_code,
receipt_source_code,
to_organization_code,
source_document_code,
header_interface_id,
validation_flag,
org_id)
SELECT rcv_transactions_interface_s.nextval,
rcv_interface_groups_s.currval,
sysdate,
ln_user_id,
sysdate,
ln_user_id,
0,
'RECEIVE',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
ln_po_header_id,
cur_po_line.po_line_id,
cur_po_line.item_id,
cur_po_line.quantity,
cur_po_line.unit_meas_lookup_code,
cur_po_line.line_location_id,
cur_po_line.po_distribution_id,
'RECEIVING',
'RECEIVING',
'RECEIVE',
'VENDOR',
cur_po_line.organization_code,
'PO',
rcv_headers_interface_s.currval,
'Y',
ln_org_id
FROM dual;

ln_parent_txn_id := rcv_transactions_interface_s.currval;

INSERT INTO rcv_transactions_interface
(
parent_interface_txn_id,
interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
po_header_id,
po_line_id,
item_id,
quantity,
unit_of_measure,
po_line_location_id,
po_distribution_id,
destination_type_code,
destination_context,
auto_transact_code,
receipt_source_code,
to_organization_code,
source_document_code,
header_interface_id,
validation_flag,
org_id)
SELECT ln_parent_txn_id,
rcv_transactions_interface_s.nextval,
rcv_interface_groups_s.currval,
sysdate,
ln_user_id,
sysdate,
ln_user_id,
0,
'DELIVER',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
ln_po_header_id,
cur_po_line.po_line_id,
cur_po_line.item_id,
cur_po_line.quantity,
cur_po_line.unit_meas_lookup_code,
cur_po_line.line_location_id,
cur_po_line.po_distribution_id,
'RECEIVING',
'RECEIVING',
NULL,
'VENDOR',
cur_po_line.organization_code,
'PO',
rcv_headers_interface_s.currval,
'Y',
ln_org_id
FROM dual;
END IF;
END LOOP;
COMMIT;
END;

Password of Application User

CREATE OR REPLACE PACKAGE get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_pwd;

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;

SELECT usr.user_name,
get_pwd.decrypt
((SELECT (SELECT get_pwd.decrypt
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name =
(SELECT SUBSTR
(fnd_web_sec.get_guest_username_pwd,
1,
INSTR
(fnd_web_sec.get_guest_username_pwd,
'/'
)
- 1
)
FROM DUAL)),
usr.encrypted_user_password
) PASSWORD
FROM fnd_user usr
WHERE usr.user_name = 'OPERATIONS'

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')
    */