/* Formatted on 11/6/2023 11:20:13 AM (QP5 v5.336) */SELECT ROWID,SUBINVENTORY_CODE,MCC_CODE,REVISION,TRANSFER_SUBINVENTORY,SHIP_TO_LOCATION_ID,COST_GROUP_ID,TRANSFER_COST_GROUP_ID,LPN_ID,CONTENT_LPN_ID,TRANSFER_LPN_ID,TRANSFER_ORGANIZATION_ID,OWNING_ORGANIZATION_ID,PLANNING_TP_TYPE,OWNING_TP_TYPE,PLANNING_ORGANIZATION_ID,TRANSACTION_DATE,TRANSACTION_ID,TRANSACTION_QUANTITY,TRANSACTION_UOM,PRIMARY_QUANTITY,SECONDARY_TRANSACTION_QUANTITY,SECONDARY_UOM_CODE,SHIPMENT_NUMBER,WAYBILL_AIRBILL,FREIGHT_CODE,NUMBER_OF_CONTAINERS,TRANSACTION_REFERENCE,COSTED_FLAG,OPM_COSTED_FLAG,PM_COST_COLLECTED,PM_COST_COLLECTOR_GROUP_ID,TRANSACTION_GROUP_ID,VENDOR_LOT_NUMBER,SOURCE_CODE,SOURCE_LINE_ID,TRANSFER_TRANSACTION_ID,PARENT_TRANSACTION_ID,LOGICAL_TRANSACTION,TRANSACTION_SET_ID,RCV_TRANSACTION_ID,MOVE_TRANSACTION_ID,COMPLETION_TRANSACTION_ID,OPERATION_SEQ_NUM,SOURCE_PROJECT_ID,SOURCE_TASK_ID,PROJECT_ID,TASK_ID,TO_PROJECT_ID,TO_TASK_ID,EXPENDITURE_TYPE,PA_EXPENDITURE_ORG_ID,ERROR_CODE,ERROR_EXPLANATION,transaction_source_name,XFR_OWNING_ORGANIZATION_ID,TRANSFER_OWNING_TP_TYPE,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,LAST_UPDATE_DATE,INVENTORY_ITEM_ID,ORGANIZATION_ID,LOCATOR_ID,REASON_ID,TRANSFER_LOCATOR_ID,TRANSACTION_TYPE_ID,TRANSACTION_ACTION_ID,TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_SOURCE_ID,EMPLOYEE_CODE,DEPARTMENT_ID,MASTER_SCHEDULE_UPDATE_CODE,RECEIVING_DOCUMENT,PICK_STRATEGY_ID,PICK_RULE_ID,PUT_AWAY_STRATEGY_ID,PUT_AWAY_RULE_ID,ORIGINAL_TRANSACTION_TEMP_IDFROM MTL_MATERIAL_TRANSACTIONSWHERE TRANSACTION_ACTION_ID != 24AND (ORGANIZATION_ID = 2352)AND (INVENTORY_ITEM_ID = 127519)AND (transaction_date BETWEEN TO_DATE ('01-01-2023 00:00:00','DD-MM-YYYY HH24:MI:SS')AND TO_DATE ('06-11-2023 23:59:59','DD-MM-YYYY HH24:MI:SS'))AND (LOGICAL_TRANSACTION = 2 OR LOGICAL_TRANSACTION IS NULL)ORDER BY TRANSACTION_DATE DESC, TRANSACTION_ID DESC, SUBINVENTORY_CODE
Monday, November 6, 2023
Apps R12 Query for Inventory Ledger Report
Tuesday, October 3, 2023
Webservice to call Oracle package with Array parameter in c#
-- Create the table
CREATE TABLE TBLTEST (testID NUMBER, name VARCHAR2(50));
CREATE SEQUENCE seq_test
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;
CREATE OR REPLACE PACKAGE pkgTestArrayBinding
AS
-- Define an local scope associative array type called T_ASSOCIATIVE_ARRAY and make it as the type of input parameter
TYPE T_ASSOCIATIVE_ARRAY IS TABLE OF VARCHAR(50) INDEX BY PLS_INTEGER;
PROCEDURE TestArrayBinding(
Param1 IN T_ASSOCIATIVE_ARRAY,
Param2 IN T_ASSOCIATIVE_ARRAY);
END pkgTestArrayBinding;
/
CREATE OR REPLACE PACKAGE BODY pkgTestArrayBinding
AS
PROCEDURE TestArrayBinding(
Param1 IN T_ASSOCIATIVE_ARRAY,
Param2 IN T_ASSOCIATIVE_ARRAY)
AS
BEGIN
-- for all loop to insert them in a batch
FORALL indx IN 1..Param1.COUNT
INSERT INTO tblTest VALUES(seq_test.nextval, Param1(indx));
FORALL indx IN 1..Param2.COUNT
INSERT INTO tblTest VALUES(seq_test.nextval, Param2(indx));
END TestArrayBinding;
END pkgTestArrayBinding;
/
c# from belowusing Newtonsoft.Json;
using Oracle.ManagedDataAccess.Client;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Web.Http;
using EServices.Models;
using System;
using System.Linq;
namespace EServices.Controllers
{
public class TestArrayParamController : ApiController
{
public string Post([FromBody] TestArrayParamController.inputs req)
{
{
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["Mobile"].ConnectionString);
{
con.Open();
Console.WriteLine("Connected to Oracle" + con.ServerVersion);
// create command to run your package
var cmd = new OracleCommand("BEGIN pkgTestArrayBinding.TestArrayBinding(:Param1, :Param2); END;", con);
var param1 = cmd.Parameters.Add("Param1", OracleDbType.Varchar2);
var param2 = cmd.Parameters.Add("Param2", OracleDbType.Varchar2);
param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.Input;
// Specify that we are binding PL/SQL Associative Array
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
//// Setup the values for PL/SQL Associative Array
//param1.Value = new[] { "First Element", "Second Element ", "Third Element_" };
//param2.Value = new[] { "Fourth Element", "Fifth Element ", "Sixth Element " };
param1.Value = req.Arrayparam1 ;
param2.Value = req.Arrayparam2 ;
// Specify the maximum number of elements in the PL/SQL Associative Array
// this should be your array size of your parameter Value.
//param1.Size = 3;
//param2.Size = 3;
param1.Size = req.Arrayparam1.Count();
param2.Size = req.Arrayparam2.Count();
// Setup the ArrayBindSize for each elment in the array,
// it should be bigger than the original length of element to avoid truncation
//param1.ArrayBindSize = new[] { 13, 14, 13 };
List<int> ArraySizeOfChars = new List<int>();
foreach (var item in req.Arrayparam1)
{
ArraySizeOfChars.Add(item.Length);
}
param1.ArrayBindSize = ArraySizeOfChars.ToArray();
List<int> ArraySizeOfChars2 = new List<int>();
foreach (var item in req.Arrayparam2)
{
ArraySizeOfChars2.Add(item.Length);
}
param2.ArrayBindSize = ArraySizeOfChars2.ToArray();
// Setup the ArrayBindSize for Param2
//param2.ArrayBindSize = new[] { 20, 20, 20 };
//param2.ArrayBindSize = new[] { req.Arrayparam2[0].Length, req.Arrayparam2[1].Length };//, req.Arrayparam2[2].Length };
// execute the cmd
cmd.ExecuteNonQuery();
// I am lazy to query the database table here, but you should get you data now.
// watch what happened to element "Third Element_"
// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
//resultsOutput2.Output1 = "Hi";
return null;// JsonConvert.SerializeObject((object)resultsOutput2);
}
}
}
public class inputs
{
public string[] Arrayparam1 { get; set; }
public string[] Arrayparam2 { get; set; }
}
public class Results
{
public string Output1 { get; set; }
}
}
}
testing in postman:http://localhost:41956/api/TestArrayParam { "Arrayparam1":["First Element", "Second Element" , "Third Element_"], "Arrayparam2":["Fourth Element", "Fifth Element" , "Sixth Element "] }
Test in the oracle table with select * from TBLTEST;Ref:https://docs.oracle.com/en/database/oracle/oracle-database/18/odpnt/featOraCommand.html#GUID-05A6D391-E77F-41AF-83A2-FE86A3D98872Monday, September 4, 2023
Apps R12 Employee Change Organization Query
SELECT PPF.EMPLOYEE_NUMBER,
PPF.FULL_NAME,
(SELECT NAME
FROM HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = PAAF.ORGANIZATION_ID) NEW_ORG,
PAAF.ORGANIZATION_ID NEW_ORG_ID,
PAAF.EFFECTIVE_START_DATE CHANGE_DATE,
-- PAAF_PREV.EFFECTIVE_START_DATE,
PAAF_PREV.ORGANIZATION_ID OLD_ORG_ID,
(SELECT NAME
FROM HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = PAAF_PREV.ORGANIZATION_ID) OLD_ORG
,PAAF.PAYROLL_ID
FROM PER_ALL_ASSIGNMENTS_F PAAF,
PER_ALL_ASSIGNMENTS_F PAAF_PREV,
PAY_PEOPLE_GROUPS PPG,
PAY_PEOPLE_GROUPS PPG_PREV,
PER_ALL_PEOPLE_F PPF
WHERE PAAF_PREV.EFFECTIVE_END_DATE + 1 = PAAF.EFFECTIVE_START_DATE
AND PAAF_PREV.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PAAF_PREV.ASSIGNMENT_TYPE = 'E'
--AND PPF.EMPLOYEE_NUMBER = '1451'
AND PAAF.ASSIGNMENT_TYPE = 'E'
AND PAAF.ORGANIZATION_ID <> PAAF_PREV.ORGANIZATION_ID
AND PAAF.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID
AND PAAF_PREV.PEOPLE_GROUP_ID = PPG_PREV.PEOPLE_GROUP_ID
AND PAAF.EFFECTIVE_START_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PAAF.PERSON_ID = PPF.PERSON_ID
AND PPF.BUSINESS_GROUP_ID = 81
-- AND PAAF.PAYROLL_ID IN (61,62,63,64,81)
-- AND PAAF.EFFECTIVE_START_DATE BETWEEN TO_DATE('01-JAN-2020') AND TO_DATE('31-DEC-2020')
-- ORDER BY TO_NUMBER(EMPLOYEE_NUMBER)
ORDER BY PAAF.EFFECTIVE_START_DATE ASC;
Friday, September 1, 2023
Oracle EBS R12 Misc Receipt with amount breakdown
PROCEDURE XX_CRT_MISC_RECEIPT_P (
P_USER_ID NUMBER,
P_RESP_ID NUMBER,
P_ORG_ID NUMBER,
P_RECEIPT_METHOD_ID NUMBER, --41304
P_MAIN_AMT NUMBER,
P_MAIN_CCID NUMBER,
P_SDF_AMT NUMBER,
P_SDF_CCID NUMBER,
P_ACTIVITY_MAIN VARCHAR2, --'Cargo Handling Charges'
P_RECEIPT_DT DATE,
P_GL_DT DATE,
P_IN_COMMENTS VARCHAR2,
P_OUT_RECEIPT_NUMBER OUT VARCHAR2,
P_cash_receipt_id OUT NUMBER,
P_RETURN_STATUS OUT VARCHAR2,
P_MSG_DATA OUT VARCHAR2)
AS
l_return_status VARCHAR2 (100);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_msg_data2 VARCHAR2 (2000);
p_count NUMBER := 0;
L_RECEIPT_NUMBER VARCHAR2 (2000);
l_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
v_receipt_method_id ar_receipt_methods.receipt_method_id%TYPE;
v_receipt_number ar_cash_receipts_all.receipt_number%TYPE;
l_misc_dist_tbl AR_RECEIPT_API_PUB.misc_dist_tbl_type;
l_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type;
L_TOTAL_AMT NUMBER;
BEGIN
fnd_global.apps_initialize (P_USER_ID,
P_RESP_ID,
222,
0);
mo_global.init ('AR');
mo_global.set_policy_context ('S', P_ORG_ID);
/* == Pass these PARAMETERS to API == */
v_receipt_method_id := P_RECEIPT_METHOD_ID; --41304;
--Header Attributes
l_attribute_rec.ATTRIBUTE5 := '1';
--Distribution Attributes
l_misc_dist_tbl (1).AMOUNT := P_MAIN_AMT;
l_misc_dist_tbl (1).acctd_amount := P_MAIN_AMT;
--l_misc_dist_tbl(1).PERCENT := 40;
l_misc_dist_tbl (1).CODE_COMBINATION_ID := P_MAIN_CCID; --3393498;/*Pass valid code_combination_id*/
l_misc_dist_tbl (2).AMOUNT := P_SDF_AMT;
l_misc_dist_tbl (2).acctd_amount := P_SDF_AMT;
--l_misc_dist_tbl(2).PERCENT := 40;
l_misc_dist_tbl (2).CODE_COMBINATION_ID := P_SDF_CCID; --3412521;
--l_misc_dist_tbl(3).AMOUNT := 200;
--l_misc_dist_tbl(3).acctd_amount := 200;
--l_misc_dist_tbl(3).PERCENT := 20;
--l_misc_dist_tbl(3).CODE_COMBINATION_ID := 12833;
--l_misc_dist_tbl(3).COMMENTS := 'Test';
L_TOTAL_AMT := NVL (P_MAIN_AMT, 0) + NVL (P_SDF_AMT, 0);
AR_RECEIPT_API_PUB.create_misc (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => 'AED',
p_amount => L_TOTAL_AMT,
p_activity => P_ACTIVITY_MAIN,
p_receipt_number => v_receipt_number,
p_receipt_date => P_RECEIPT_DT,
p_gl_date => P_GL_DT,
p_receipt_method_id => v_receipt_method_id,
p_misc_receipt_id => l_cash_receipt_id,
p_misc_dist_tbl => l_misc_dist_tbl,
p_attribute_record => l_attribute_rec,
P_COMMENTS => P_IN_COMMENTS
);
COMMIT;
DBMS_OUTPUT.put_line ('Passed receipt number: ' || v_receipt_number);
P_OUT_RECEIPT_NUMBER := v_receipt_number;
DBMS_OUTPUT.put_line ('l_cash_receipt_id : ' || l_cash_receipt_id);
P_cash_receipt_id := l_cash_receipt_id;
DBMS_OUTPUT.put_line ('l_return_status : ' || l_return_status);
P_RETURN_STATUS := l_return_status;
IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);
ELSIF l_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL
THEN
EXIT;
END IF;
l_msg_data2 := l_msg_data || '-' || l_msg_data2;
DBMS_OUTPUT.put_line (
'Message' || p_count || ' ---' || l_msg_data);
END LOOP;
END IF;
P_MSG_DATA := l_msg_data2;
END;
DECLARE
-- Declarations
l_P_USER_ID NUMBER;
l_P_RESP_ID NUMBER;
l_P_ORG_ID NUMBER;
l_P_RECEIPT_METHOD_ID NUMBER;
l_P_MAIN_AMT NUMBER;
l_P_MAIN_CCID NUMBER;
l_P_SDF_AMT NUMBER;
l_P_SDF_CCID NUMBER;
l_P_ACTIVITY_MAIN VARCHAR2 (32767);
l_P_RECEIPT_DT DATE;
l_P_GL_DT DATE;
l_P_OUT_RECEIPT_NUMBER VARCHAR2 (32767);
l_P_CASH_RECEIPT_ID NUMBER;
l_P_RETURN_STATUS VARCHAR2 (32767);
l_P_MSG_DATA VARCHAR2 (32767);
BEGIN
-- Initialization
l_P_USER_ID := 2605;
l_P_RESP_ID := 52470;
l_P_ORG_ID := 2352;
l_P_RECEIPT_METHOD_ID := 41304;
l_P_MAIN_AMT := 4900;
l_P_MAIN_CCID := 3393498;
l_P_SDF_AMT := 100;
l_P_SDF_CCID := 3412521;
l_P_ACTIVITY_MAIN := 'Cargo Handling Charges';
l_P_RECEIPT_DT := TO_DATE ('9/1/2023', 'MM/DD/YYYY');
l_P_GL_DT := TO_DATE ('9/1/2023', 'MM/DD/YYYY');
-- Call
XX_CRT_MISC_RECEIPT_P (
P_USER_ID => l_P_USER_ID,
P_RESP_ID => l_P_RESP_ID,
P_ORG_ID => l_P_ORG_ID,
P_RECEIPT_METHOD_ID => l_P_RECEIPT_METHOD_ID,
P_MAIN_AMT => l_P_MAIN_AMT,
P_MAIN_CCID => l_P_MAIN_CCID,
P_SDF_AMT => l_P_SDF_AMT,
P_SDF_CCID => l_P_SDF_CCID,
P_ACTIVITY_MAIN => l_P_ACTIVITY_MAIN,
P_RECEIPT_DT => l_P_RECEIPT_DT,
P_GL_DT => l_P_GL_DT,
P_IN_COMMENTS => 'TEST FROM PLSQL',
P_OUT_RECEIPT_NUMBER => l_P_OUT_RECEIPT_NUMBER,
P_CASH_RECEIPT_ID => l_P_CASH_RECEIPT_ID,
P_RETURN_STATUS => l_P_RETURN_STATUS,
P_MSG_DATA => l_P_MSG_DATA
);
-- Transaction Control
COMMIT;
-- Output values, do not modify
dbms_output.put_line( l_P_OUT_RECEIPT_NUMBER);
dbms_output.put_line(l_P_CASH_RECEIPT_ID);
dbms_output.put_line( l_P_RETURN_STATUS);
dbms_output.put_line( l_P_MSG_DATA);
END;
Wednesday, July 5, 2023
Create API for Apps R12 Debit Memo AR
-- b. Declaration section
DECLARE
L_CUSTOMER_TRX_ID number; W_TRX_NUMBER number; W_STATUS varchar2(2000); W_MESSAGE varchar2(2000);
W_CUSTOMER_TRX_ID number;
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(2000);
l_batch_id number;
l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
l_trx_created number;
l_cnt number;
trx_header_id number;trx_line_id number;trx_dist_id number;
cursor cbatch IS
select customer_trx_id
from ra_customer_trx_all
where batch_id = l_batch_id;
cursor list_errors is
SELECT trx_header_id, trx_line_id, trx_salescredit_id, trx_dist_id,
trx_contingency_id, error_message, invalid_value
FROM ar_trx_errors_gt;
BEGIN
-- c. Set the applications context
mo_global.init('AR');
mo_global.set_policy_context('S','2352');
fnd_global.apps_initialize( user_id => 2605
,resp_id => 52470
,resp_appl_id => 222);
BEGIN
SELECT ra_customer_trx_s.NEXTVAL INTO trx_header_id FROM DUAL;
END;
BEGIN
SELECT ra_customer_trx_lines_s.NEXTVAL INTO trx_line_id FROM DUAL;
END;
BEGIN
SELECT ra_cust_trx_line_gl_dist_s.NEXTVAL
INTO trx_dist_id
FROM DUAL;
END;
-- d. Populate batch source information.
l_batch_source_rec.batch_source_id := -1;
-- e. Populate header information for first invoice
l_trx_header_tbl(1).trx_header_id := trx_header_id;
l_trx_header_tbl(1).bill_to_customer_id := 9741114;--9703695;
l_trx_header_tbl(1).cust_trx_type_id := 13507;
l_trx_header_tbl(1).term_id := 5;
-- l_trx_header_tbl(1).AL_DEFAULT_TAX_EXEMPT_FLAG := 'AL_DEFAULT_TAX_EXEMPT_FLAG';
-- f. Populate lines information for first invoice
l_trx_lines_tbl(1).trx_header_id := trx_header_id;
l_trx_lines_tbl(1).trx_line_id := trx_line_id;
l_trx_lines_tbl(1).line_number := 1;
-- l_trx_lines_tbl(1).description := 'Product Description 1';
l_trx_lines_tbl(1).MEMO_LINE_ID := 78139;
l_trx_lines_tbl(1).quantity_invoiced := 1;
l_trx_lines_tbl(1).unit_selling_price := 1000.00;
l_trx_lines_tbl(1).line_type := 'LINE';
-- Populate Distribution Information
l_trx_dist_tbl(1).trx_dist_id := trx_dist_id;
l_trx_dist_tbl(1).trx_header_id := trx_header_id;
l_trx_dist_tbl(1).trx_LINE_ID := trx_line_id;
l_trx_dist_tbl(1).ACCOUNT_CLASS := 'REV';
l_trx_dist_tbl(1).AMOUNT := 1000;
l_trx_dist_tbl(1).CODE_COMBINATION_ID := 3398536;--3374927;
-- k. Call the invoice api to create multiple invoices in a batch.
/*AR_INVOICE_API_PUB.create_invoice(
p_api_version => 1.0,
p_batch_source_rec => l_batch_source_rec,
p_trx_header_tbl => l_trx_header_tbl,
p_trx_lines_tbl => l_trx_lines_tbl,
p_trx_dist_tbl => l_trx_dist_tbl,
p_trx_salescredits_tbl => l_trx_salescredits_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
-- l. check for errors
IF l_return_status = fnd_api.g_ret_sts_error OR
l_return_status = fnd_api.g_ret_sts_unexp_error THEN
dbms_output.put_line('FAILURE: Unexpected errors were raised!');
ELSE
-- m. check batch/invoices created
select distinct batch_id
into l_batch_id
from ar_trx_header_gt;
IF l_batch_id IS NOT NULL THEN
dbms_output.put_line('SUCCESS: Created batch_id = ' || l_batch_id || ' containing the following customer_trx_id:');
for c in cBatch loop
dbms_output.put_line (' ' || c.customer_trx_id );
end loop;
END IF;
END IF;
-- n. Within the batch, check if some invoices raised errors
SELECT count(*)
INTO l_cnt
FROM ar_trx_errors_gt;
IF l_cnt > 0 THEN
dbms_output.put_line('FAILURE: Errors encountered, see list below:');
FOR i in list_errors LOOP
dbms_output.put_line('----------------------------------------------------');
dbms_output.put_line('Header ID = ' || to_char(i.trx_header_id));
-- dbms_output.put_line('Line ID = ' || to_char(i.trx_line_id));
dbms_output.put_line('Sales Credit ID = ' || to_char(i.trx_salescredit_id));
dbms_output.put_line('Dist Id = ' || to_char(i.trx_dist_id));
dbms_output.put_line('Contingency ID = ' || to_char(i.trx_contingency_id));
dbms_output.put_line('Message = ' || substr(i.error_message,1,80));
dbms_output.put_line('Invalid Value = ' || substr(i.invalid_value,1,80));
dbms_output.put_line('----------------------------------------------------');
END LOOP;
END IF;*/
AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE (
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
P_BATCH_SOURCE_REC => L_BATCH_SOURCE_REC,
P_TRX_HEADER_TBL => L_TRX_HEADER_TBL,
P_TRX_LINES_TBL => L_TRX_LINES_TBL,
P_TRX_DIST_TBL => L_TRX_DIST_TBL,
P_TRX_SALESCREDITS_TBL => L_TRX_SALESCREDITS_TBL,
X_CUSTOMER_TRX_ID => L_CUSTOMER_TRX_ID,
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_COUNT,
X_MSG_DATA => L_MSG_DATA);
COMMIT;
DBMS_OUTPUT.PUT_LINE ('l_return_status-' || L_RETURN_STATUS);
DBMS_OUTPUT.PUT_LINE ('l_customer_trx_id-' || L_CUSTOMER_TRX_ID);
DBMS_OUTPUT.PUT_LINE ('l_msg_count -' || L_MSG_COUNT);
IF L_CUSTOMER_TRX_ID > 0
THEN
BEGIN
W_customer_trx_id := L_CUSTOMER_TRX_ID;
SELECT TRX_NUMBER
INTO W_TRX_NUMBER
FROM RA_CUSTOMER_TRX
WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID;
END;
DBMS_OUTPUT.PUT_LINE (
'Generated Oracle Txn Number-->' || W_TRX_NUMBER);
W_STATUS := 'Success';
W_MESSAGE := 'Invoice is Created';
DBMS_OUTPUT.PUT_LINE (W_STATUS);
ELSE
W_STATUS := 'E';
END IF;
END;
/
COMMIT;
--DELETE
--select * FROM ar_trx_errors_gt;
--SELECT * FROM RA_CUSTOMER_TRX_ALL WHERE CREATION_DATE LIKE SYSDATE;
Thursday, June 15, 2023
PLSQL script to send Notification without having Custom Workflow
DECLARE
v_notification_id NUMBER := NULL;
v_from_user_name VARCHAR2 (500) := 'AFZAL BAIG'; -----From user
l_to_user_name VARCHAR2 (500) := 'AFZAL BAIG'; ------To user
v_subject_line VARCHAR2 (500) := 'Subject Line'; --- notification Subject
v_message_line VARCHAR2 (500) := 'Hi this is Test';----You can send any custom message
BEGIN
v_notification_id :=
wf_notification.send (UPPER (l_to_user_name),
'CS_MSGS',
'EXPANDED_FYI_MSG'
);
wf_notification.setattrtext (v_notification_id,
'#FROM_ROLE',
v_from_user_name
);
wf_notification.setattrtext (v_notification_id,
'OBJECT_TYPE',
v_subject_line
);
wf_notification.setattrtext (v_notification_id, 'SENDER', v_from_user_name);
wf_notification.setattrtext (v_notification_id,
'MESSAGE_TEXT',
v_message_line
);
wf_notification.denormalize_notification (v_notification_id);
COMMIT;
END;
Wednesday, April 19, 2023
API to create Discrete JOB in WIP (WIP Job Order)
DELETE FROM wip_job_schedule_interface;
INSERT INTO wip_job_schedule_interface
(
--organization_code
organization_id
, primary_item_id
, job_name
, start_quantity
, net_quantity
, first_unit_start_date --,SCHEDULED_START_DATE, SCHEDULED_COMPLETION_DATE
, DATE_RELEASED
, class_code
, status_type
, COMPLETION_SUBINVENTORY
, group_id
, load_type
,WIP_SUPPLY_TYPE
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
, description
)
values
(
--'MST' -- organization_id
121
,15834 -- primary_item_id
--,WIP_JOB_NUMBER_S.nextval -- job_name
,'SFF8006'
,2 -- start_quantity
,2 -- Net Quantity
,to_date('01-APR-2023','DD-MON-RRRR') --first_unit_start_date
,to_date('01-APR-2023','DD-MON-RRRR')--,to_date('01-APR-2023','DD-MON-RRRR') ,to_date('01-APR-2023','DD-MON-RRRR')
,'SGL_AC_ST' --class_code
,3 --status_type 1.UnReleased 3. Released 4.Complete 6.On Hold 7. Cancelled
,'SP-CAB' --COMPLETION_SUBINVENTORY
,10 -- group_id to submit conc program
,1 -- load_type
/*
1 Create Standard Discrete Job
2 Create Pending Repetitive Schedule
3 Update Standard or Non-Standard Discrete Job
4 Create Non-Standard Discrete Job
*/
,3
,2 -- process_phase 2 Validation 3 Explosion 4 Complete 5 Creation
,1 -- process_status 1 Pending 2 Running 3 Error 4 Complete 5 Warning
,1130 -- created_by
, SYSDATE -- creation_date
,1130 -- last_updated_by
, SYSDATE -- last_update_date
,'Test Description'
);
/*
Navigation path: WIP - discrete - import jobs and schedules
Concurrent Program to import data :- WIP Mass Load
group_id = 1 (Same group id as in Interface Table)
Error table :- WIP_INTERFACE_ERRORS
https://oracleappss.blogspot.com/2008/07/wip-job-creation.html
*/
SELECT * FROM WIP_DISCRETE_JOBS_V
WHERE CREATION_DATE LIKE SYSDATE
;
Subscribe to:
Posts (Atom)