Oracle Apps KT: 2019

Monday, December 23, 2019

Excel Trim remove spaces not working then use TRIM(SUBSTITUTE(C1,CHAR(160),CHAR(32)))

Step1: Formula: =TRIM(SUBSTITUTE(C1,CHAR(160),CHAR(32)))

C1 is the value

Step2: Copy all the column, replace with value.

Step3: convert text to number.

Sunday, November 17, 2019

Oracle Apps R12 query for attachments from backend for sshr txns

1. get the item_key, for workflow administrator

select * from hr_api_transactions
where item_key = '217855'

2. get the transaction_id and other details from query 1,
the transaction_id can be in pk5_value or pk1_value for the below query 2.
if not then query based on created_by, creation_date from query 1.

select fd.*, fl.*--,fad.*--fd.*,
from fnd_attached_documents fad
,fnd_documents fd
,fnd_lobs fl
,fnd_document_datatypes fdd
,fnd_document_categories_tl fdct
where fad.document_id = fd.document_id
and fd.media_id = fl.file_id
and fd.datatype_id = fdd.datatype_id
and fd.category_id = fdct.category_id
and fdd.user_name = 'File'
--and fad.entity_name = 'XXASSET' -- replace with the entity_name you want to extract.
and fdct.language = 'US'
--and pk5_value = '217855'
and fad.creation_date like to_date('27-oct-2019')
and fad.created_by = 12345

and fd.creation_date like sysdate

Sunday, October 27, 2019

Solution for xml output of item number type with prefix 00, showing output in excel without prefix

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"> <?XX_XML_TAG?> </fo:bidi-override>

Saturday, September 28, 2019

Oracle Apps R12 Query for Employee contact relationship and details.

SELECT papf.person_id employee_id, papf.full_name employee_name,papf.employee_number,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,pcr.contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date, papf_cont.date_of_birth contact_dob, papf_cont.sex
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont --contact info also saved in per_all_people_f without emp_num
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type
and papf.employee_number = '1234'

html to get co-ordinates (latitude/longitude) and validation around +/- 50 meters.

<!DOCTYPE html>
<html>
<body>

Click the button to get your coordinates.

<button onclick="getLocation()">Check In</button>



<script>
var x = document.getElementById("demo");

function getLocation() {
if (navigator.geolocation) {
navigator.geolocation.getCurrentPosition(showPosition);
} else {
x.innerHTML = "Geolocation is not supported by this browser.";
}
}

function showPosition(position) {

// x.innerHTML = "Latitude: " + position.coords.latitude +
//" Longitude: " + position.coords.longitude;

var meters = 50;
var off_latitude = 85.1288229;
var off_longitude = 89.3264959;

var curr_latitude = position.coords.latitude;
var curr_longitude = position.coords.longitude;

var coef = meters * 0.0000089;

var plus50_lat = off_latitude + coef;
// pi / 180 d= 0.018
var plus50_long = off_longitude + coef / Math.cos(off_latitude * 0.018);

var minus50_lat = off_latitude - coef;
var minus50_long = off_longitude - coef / Math.cos(off_latitude * 0.018);

if (
((curr_latitude <= off_latitude && curr_latitude >= minus50_lat)
||(curr_latitude <= plus50_lat && curr_latitude >= off_latitude)
)

&& (curr_longitude >= off_longitude && curr_longitude <= plus50_long) )
{
x.innerHTML = "You are in the office, good Morning" + " "+ "CurrLatitude: "+ curr_latitude + " CurrLongitude: " +curr_longitude
+ " "+ "off_latitude : "+ off_latitude + " off_longitude : " + off_longitude
+ " "+ "plus50_lat : "+ plus50_lat + " plus50_long : " + plus50_long + " "+ "minus50_lat : "+ minus50_lat + " minus50_long : " + minus50_long ;
}

else
{
x.innerHTML = "You are not in the office, please recheck"+ " "+ "CurrLatitude: "+ curr_latitude + " CurrLongitude: " +curr_longitude
+ " "+ "off_latitude : "+ off_latitude + " off_longitude : " + off_longitude
+ " "+ "plus50_lat : "+ plus50_lat + " plus50_long : " + plus50_long + " "+ "minus50_lat : "+ minus50_lat + " minus50_long : " + minus50_long ;
}

}
</script>

</body>
</html>

Wednesday, May 8, 2019

Oracle Apps R12 Function to get query rows in single variable

CREATE OR REPLACE function APPS.F_CONTACT_MSG(P_DAYS IN NUMBER)

return varchar2 is

CURSOR C1 IS

SELECT 'TXN_NUMBER' , 'CUSTOMER_DETAILS' FROM DUAL

UNION ALL

SELECT TRX_NUMBER, PARTY_NAME||' '||END_DATE FROM F_AR_TRX_CUST_DETAILS_V

WHERE END_DT = TRUNC(SYSDATE) + P_DAYS

;

M_LOCN_CODE VARCHAR2(20000);

M_STK_QTY_BU VARCHAR2(20000);

--M_REF_DEL_DT OW_FS.DT%TYPE;

M_TEMP1 NUMBER := 0;

M_REF_CODE_NO VARCHAR2(20000);

begin

IF C1%ISOPEN THEN

CLOSE C1;

END IF;

OPEN C1;

LOOP

FETCH C1 INTO M_LOCN_CODE, M_STK_QTY_BU;--, M_REF_DEL_DT;

EXIT WHEN C1%NOTFOUND;

M_TEMP1 := M_TEMP1 + 1;

IF M_TEMP1 = 1 THEN

M_REF_CODE_NO := M_LOCN_CODE||' '||M_STK_QTY_BU;

ELSE

M_REF_CODE_NO := M_REF_CODE_NO || CHR(13)|| CHR(10)|| M_LOCN_CODE||' '||M_STK_QTY_BU;

END IF;

END LOOP;

RETURN NVL(M_REF_CODE_NO,'');

end;

USAGE :

In any block, define variable like below and use it.

L_RETURN VARCHAR2(2000):= F_CONTACT_MSG(P_DAYS);

Monday, May 6, 2019

Oracle Apps R12 API to create AR Receipt

CREATE OR REPLACE PROCEDURE APPS.XXCUS_CRT_ARRECEIPT ( -- errbuff OUT VARCHAR2
--,retcode OUT VARCHAR2
P_CUSTOMER_ID IN NUMBER
,p_amount IN NUMBER
,p_receipt_date IN DATE
,p_deposit_date IN DATE
,p_gl_date IN DATE
,p_maturity_date IN DATE
,p_remittance_bank_account_id NUMBER --10003; -- بنك الفجيره الوطنى
/*
select REMIT_BANK_ACCT_USE_ID from ar_receipt_method_accounts_all
where org_id = 145
and receipt_method_id = 7019
*/
,P_COMMENTS IN VARCHAR2
--,p_customer_number IN VARCHAR2
--,p_Cust_Name IN OUT VARCHAR2
,p_receipt_number OUT VARCHAR2
,W_MID OUT VARCHAR2
,W_Status OUT VARCHAR2
,W_Message OUT VARCHAR2

)IS

l_user_id NUMBER := 26051;
l_resp_id NUMBER := 506011;
l_resp_appl_id NUMBER := 222;
l_org_id NUMBER := 1454;
--l_receipt_method_id NUMBER := 70197; --METHOD_NAME
p_global_attribute_rec AR_RECEIPT_API_PUB.global_attribute_rec_type;
p_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type;

p_currency_code VARCHAR2(100) := 'AED';
-- p_receipt_date DATE := TRUNC(SYSDATE);
-- p_gl_date DATE := TRUNC(SYSDATE);
-- p_deposit_date DATE := TRUNC(SYSDATE);
p_override_remit_account_flag VARCHAR2(100) := 'N';
--p_maturity_date DATE := TRUNC(SYSDATE);
-- p_comments VARCHAR2(100) := 'FROM WS-4';

---------------LOCAL VARIABLES-----------
p_payroll_end_dt date ;
p_api_version NUMBER;
p_init_msg_list VARCHAR2(240);
p_commit VARCHAR2(240);
p_validation_level NUMBER;
p_usr_currency_code VARCHAR2(240);
p_usr_exchange_rate_type VARCHAR2(240);
p_exchange_rate_type VARCHAR2(240);
p_exchange_rate NUMBER;
p_exchange_rate_date DATE;
p_factor_discount_amount NUMBER;
--p_receipt_date DATE;
p_postmark_date DATE;
p_customer_number VARCHAR2(240);
p_customer_bank_account_id NUMBER;
p_customer_bank_account_num VARCHAR2(240);
p_customer_bank_account_name VARCHAR2(240);
p_location VARCHAR2(240);
p_customer_receipt_reference VARCHAR2(240);
p_remittance_bank_account_num VARCHAR2(240);
p_remittance_bank_account_name VARCHAR2(240);
p_receipt_method_name VARCHAR2(240);
p_doc_sequence_value NUMBER;
p_ussgl_transaction_code VARCHAR2(240);
p_anticipated_clearing_date DATE;
p_called_from VARCHAR2(240);
--p_comments VARCHAR2(240);
p_issuer_name VARCHAR2(240);
p_issue_date DATE;
p_issuer_bank_branch_id NUMBER;
--p_amount NUMBER;
--p_receipt_number VARCHAR2(240);
p_receipt_method_id NUMBER := 7019; --Cاجمالى النقد FM
p_customer_name VARCHAR2(240);
--p_customer_id NUMBER;
--p_currency_code VARCHAR2(10);
--p_gl_date DATE;
--p_deposit_date DATE;
p_customer_site_use_id NUMBER;
--p_override_remit_account_flag VARCHAR2(1);
--p_remittance_bank_account_id NUMBER;
--p_maturity_date DATE;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(240);
p_cr_id NUMBER;
l_receipt_exists VARCHAR2(1);
l_customer_exists VARCHAR2(1);
l_exists_receipt_number VARCHAR2(30);

BEGIN

BEGIN

fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_id,0);
mo_global.set_policy_context('S',l_org_id);
mo_global.init ('AR');
p_attribute_rec.ATTRIBUTE5 := '1';

SELECT 'Y'
INTO l_customer_exists
FROM AR_CUSTOMERS ARC
where arc.customer_id = p_customer_id;

--where ARC.CUSTOMER_NUMBER = p_customer_number;

EXCEPTION WHEN OTHERS THEN
l_customer_exists := 'N';

END;

IF l_customer_exists = 'Y' then

p_receipt_number := 'WS-'||XX_CUST_CR_REC_S.NEXTVAL; --rECEIPT NUMBER

AR_RECEIPT_API_PUB.Create_cash (
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_usr_currency_code => p_usr_currency_code
,p_currency_code => p_currency_code
,p_usr_exchange_rate_type => p_usr_exchange_rate_type
,p_exchange_rate_type => p_exchange_rate_type
,p_exchange_rate => p_exchange_rate
,p_exchange_rate_date => p_exchange_rate_date
,p_amount => p_amount
,p_factor_discount_amount => p_factor_discount_amount
,p_receipt_number => p_receipt_number
,p_receipt_date => p_receipt_date
,p_gl_date => p_gl_date
,p_maturity_date => p_maturity_date
,p_postmark_date => p_postmark_date
,p_customer_id => p_customer_id
,p_customer_name => p_customer_name
,p_customer_number => p_customer_number
,p_customer_bank_account_id => p_customer_bank_account_id
,p_customer_bank_account_num => p_customer_bank_account_num
,p_customer_bank_account_name => p_customer_bank_account_name
,p_location => p_location
,p_customer_site_use_id => p_customer_site_use_id
,p_customer_receipt_reference => p_customer_receipt_reference
,p_override_remit_account_flag => p_override_remit_account_flag
,p_remittance_bank_account_id => p_remittance_bank_account_id
,p_remittance_bank_account_num => p_remittance_bank_account_num
,p_remittance_bank_account_name => p_remittance_bank_account_name
,p_deposit_date => p_deposit_date
,p_receipt_method_id => p_receipt_method_id
,p_receipt_method_name => p_receipt_method_name
,p_doc_sequence_value => p_doc_sequence_value
,p_ussgl_transaction_code => p_ussgl_transaction_code
,p_anticipated_clearing_date => p_anticipated_clearing_date
,p_called_from => p_called_from
,p_global_attribute_rec => p_global_attribute_rec
,p_attribute_rec => p_attribute_rec
,p_comments => p_comments
,p_issuer_name => p_issuer_name
,p_issue_date => p_issue_date
,p_issuer_bank_branch_id => p_issuer_bank_branch_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_cr_id => p_cr_id
,p_org_id => l_org_id
);

IF (x_return_status = 'S') THEN

COMMIT;

--fnd_file.PUT_LINE(FND_FILE.OUTPUT,'==================MAIN EXCEPTIONS=================');

dbms_output.put_line('SUCCESS ReceiptNum >'||p_receipt_number);
dbms_output.put_line('Return Status = '|| SUBSTR (x_return_status,1,255));
dbms_output.put_line('p_cr_id = '||p_cr_id);
p_receipt_number := p_receipt_number;

BEGIN
SELECT CASH_RECEIPT_ID INTO W_MID
--,RECEIPT_NUMBER,RECEIPT_METHOD_ID,ORG_ID,CREATION_DATE
FROM AR_CASH_RECEIPTS_ALL
WHERE RECEIPT_NUMBER = p_receipt_number--'98107'
AND TRUNC(CREATION_DATE) LIKE SYSDATE
AND RECEIPT_METHOD_ID = p_receipt_method_id
--and type = 'MISC'
;
EXCEPTION WHEN OTHERS THEN NULL;
END;

--W_MID := 'Created from WS';
W_Status := 'S';
W_Message := 'Return Status = '|| SUBSTR (x_return_status,1,255) ;

ELSE

ROLLBACK;

dbms_output.put_line('Return Status = '|| SUBSTR (x_return_status,1,255));
dbms_output.put_line('Message Count = '|| TO_CHAR(x_msg_count ));
dbms_output.put_line('Message Data = '|| SUBSTR (x_msg_data,1,255));
dbms_output.put_line(APPS.FND_MSG_PUB.Get ( p_msg_index => APPS.FND_MSG_PUB.G_LAST,
p_encoded => APPS.FND_API.G_FALSE));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Exception-1 Customer Number:'||p_customer_number||'-'||sqlerrm);
p_receipt_number := NULL;
W_MID := 'Not Created';
W_Status := 'N';
W_Message := 'Message Data = '|| SUBSTR (x_msg_data,1,255) ;

END IF;

ELSE
dbms_output.put_line('customer doesnt exists'||sqlerrm);
p_receipt_number := NULL;
W_MID := 'Not Created';
W_Status := 'N';
W_Message := 'Customer Not Exists = '|| SUBSTR (sqlerrm,1,255) ;

END IF;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception :'||sqlerrm);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Exception-Last Customer Numebr:'||p_customer_number||'-'||sqlerrm);
p_receipt_number := NULL;
W_MID := 'Not Created';
W_Status := 'N';
W_Message := 'Message Data = '|| SUBSTR (sqlerrm,1,255) ;

END;

/

Oracle Apps R12 API to Create AR Invoice

create or replace procedure XXCRT_ARINV
(
P_DATE DATE,
P_BILLING_ID NUMBER,
P_CUSTOMER_ID NUMBER,
W_TRX_NUMBER OUT VARCHAR2,
W_STATUS OUT VARCHAR2,
W_MESSAGE OUT VARCHAR2
)
as
L_CNT VARCHAR2(100);
P_RESULT VARCHAR2 (300);
--P_DATE DATE := TRUNC(SYSDATE) +1;
l_batch_source_id NUMBER := 1003; --Source ???????
l_cust_trx_type_id NUMBER := 1001; --type_id ???????
v_customer_id NUMBER := P_CUSTOMER_ID;--8173483;--
--L_CUSTOMER_TRX_ID NUMBER := P_CUSTOMER_TRX_ID;--8777389;--
v_user_id NUMBER := 26055; --FND_PROFILE.VALUE('USER_ID'); --2605;
v_resp_id NUMBER := 506035; --FND_PROFILE.VALUE('RESP_ID');--52328;
v_resp_appl_id NUMBER := 222; --fnd_global.resp_appl_id;
v_org_id NUMBER := 1452; --FND_PROFILE.VALUE('ORG_ID');--1930;
v_date DATE := P_DATE;
v_trx_number 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_contingencies_tbl ar_invoice_api_pub.trx_contingencies_tbl_type;
trx_header_id NUMBER;
trx_line_id NUMBER;
trx_dist_id NUMBER;
v_line NUMBER := 1;
v_line_count NUMBER := 0;
v_price_total NUMBER := 0;

l_currency_code VARCHAR2 (100) := 'AED';
l_site_use_id NUMBER;
L_EXIST VARCHAR2 (100);
L_MEMO_LINE_ID NUMBER;
--W_TRX_NUMBER VARCHAR2 (100);
--W_STATUS VARCHAR2 (100);
--W_MESSAGE VARCHAR2 (100);
--LIST_ERRORS VARCHAR2 (100);

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;

CURSOR cbatch IS
SELECT customer_trx_id
FROM ra_customer_trx_all
WHERE batch_id = l_batch_id;

CURSOR cvalidtxn IS
SELECT trx_header_id
FROM ar_trx_header_gt
WHERE trx_header_id NOT IN
(SELECT trx_header_id FROM ar_trx_errors_gt);

CURSOR HEADER_DETAILS IS
--custom table storing the details of header info
SELECT * FROM
XX_BILLING_SYS_R
WHERE BILLING_ID = P_BILLING_ID ;

CURSOR line_items IS
--custom table storing the details of line info
SELECT MEMO_LINE_ID,
UNIT_SELLING_PRICE PRICE,
QUANTITY_INVOICED QTY,
UOM_CODE,
GL_ID,
DESCRIPTION NAME
FROM XX_BILLING_SYS_LINES RCL
WHERE RCL.BILLING_ID = P_BILLING_ID;

--end of cursors
BEGIN

BEGIN
SELECT ra_customer_trx_s.NEXTVAL INTO trx_header_id FROM DUAL;
END;

BEGIN

SELECT c.site_use_id
INTO l_site_use_id
FROM hz_cust_accounts a,
hz_cust_acct_sites_all b,
hz_cust_site_uses_all c,
hz_party_sites d,
hz_locations e
WHERE a.cust_account_id = b.cust_account_id
AND b.cust_acct_site_id = c.cust_acct_site_id
AND b.party_site_id = d.party_site_id
AND d.location_id = e.location_id
AND c.site_use_code = 'BILL_TO'
AND c.status = 'A'
AND b.org_id = c.org_id
AND b.org_id = v_org_id
AND a.cust_account_id = P_customer_id; --8173483

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('ERROR IN SITE--22');
END;

FOR H IN HEADER_DETAILS LOOP
fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
mo_global.init ('AR');
mo_global.set_policy_context ('S', v_org_id);
xla_security_pkg.set_security_context (v_resp_appl_id);
DBMS_OUTPUT.put_line ('l_cust_trx_type_id' || l_cust_trx_type_id);
DBMS_OUTPUT.put_line ('bill_to_customer_id' || p_customer_id);
DBMS_OUTPUT.put_line ('l_site_use_id' || l_site_use_id);
l_trx_header_tbl (1).trx_header_id := trx_header_id;
l_trx_header_tbl (1).bill_to_customer_id := v_customer_id;
l_trx_header_tbl (1).cust_trx_type_id := l_cust_trx_type_id;
l_trx_header_tbl (1).trx_date := TRUNC (p_date);
l_trx_header_tbl (1).gl_date := TRUNC (p_date);
l_trx_header_tbl (1).trx_currency := l_Currency_Code;
l_trx_header_tbl (1).primary_salesrep_id := -3;
l_trx_header_tbl (1).org_id := v_org_id;
l_trx_header_tbl (1).comments := 'Test from plsql Comments' || ' ' || SYSDATE;
l_trx_header_tbl (1).INTERFACE_HEADER_CONTEXT := h.INTERFACE_HEADER_CONTEXT;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE1 := h.INTERFACE_HEADER_ATTRIBUTE1;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE2 := h.INTERFACE_HEADER_ATTRIBUTE2;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE3 := h.INTERFACE_HEADER_ATTRIBUTE3;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE4 := h.INTERFACE_HEADER_ATTRIBUTE4;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE5 := h.INTERFACE_HEADER_ATTRIBUTE5;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE6 :=h.INTERFACE_HEADER_ATTRIBUTE6;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE7 :=h.INTERFACE_HEADER_ATTRIBUTE7;

l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE12 := h.INTERFACE_HEADER_ATTRIBUTE12;--TO_CHAR(fnd_conc_date.string_to_date(h.INTERFACE_HEADER_ATTRIBUTE13) + 1,'yyyy/mm/dd hh:mi:ss');--'2019/01/01 00:00:00';

l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE13 := h.INTERFACE_HEADER_ATTRIBUTE13;--TO_CHAR(ADD_MONTHS(fnd_conc_date.string_to_date(h.INTERFACE_HEADER_ATTRIBUTE13),12) ,'yyyy/mm/dd hh:mi:ss');--'2019/05/13 00:00:00';
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE14 := h.INTERFACE_HEADER_ATTRIBUTE14;
--l_trx_header_tbl(1).attribute11:=p_Source;
--l_trx_header_tbl(1).attribute12:=p_trx_number;
--l_trx_header_tbl(1).attribute13:=TRUNC(SYSDATE);
--l_trx_header_tbl(1).attribute14:=TRUNC(P_Expiration_Date);
--l_trx_header_tbl(1).attribute15:=p_Accounting_Rule;
--l_trx_header_tbl(1).attribute10:=p_Sub_Service;
--l_trx_header_tbl(1).attribute7:=NVL(substr(p_business_name,0,60),'-');
l_trx_header_tbl (1).trx_class := 'INV';
l_trx_header_tbl (1).bill_to_site_use_id := l_site_use_id;
l_trx_header_tbl (1).term_id := 1000;
l_batch_source_rec.batch_source_id := l_batch_source_id;

END LOOP;

FOR i IN line_items
LOOP
BEGIN
v_line_count := v_line_count + v_line;
--v_price_total := v_price_total + (i.price * i.qty);
l_trx_lines_tbl (v_line_count).trx_header_id := trx_header_id;
l_trx_lines_tbl (v_line_count).trx_line_id :=
ra_customer_trx_lines_s.NEXTVAL; --trx_line_id_v;
l_trx_lines_tbl (v_line_count).line_number := v_line_count;
l_trx_lines_tbl (v_line_count).description := i.name;
l_trx_lines_tbl (v_line_count).quantity_invoiced := i.qty;
l_trx_lines_tbl (v_line_count).unit_selling_price := i.price;
l_trx_lines_tbl (v_line_count).line_type := 'LINE';
--l_trx_lines_tbl (1).tax_exempt_flag := 'E';
--l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE := 'E';
--l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE_MEANING := 'E';
l_trx_lines_tbl (v_line_count).memo_line_id := i.memo_line_id;
-- l_trx_lines_tbl (1).TAX_PRECEDENCE := 10;
-- l_trx_lines_tbl (1).TAX_RATE := 10;
l_trx_dist_tbl (v_line_count).trx_dist_id :=
ra_cust_trx_line_gl_dist_s.NEXTVAL;
l_trx_dist_tbl (v_line_count).trx_line_id :=
ra_customer_trx_lines_s.CURRVAL;
l_trx_dist_tbl (v_line_count).account_class := 'REV';
l_trx_dist_tbl (v_line_count).PERCENT := 100;
l_trx_dist_tbl (v_line_count).code_combination_id := i.gl_id;
--DBMS_OUTPUT.put_line ('i value ->' || i);
--DBMS_OUTPUT.put_line ('line- ' || i || ' Amt = ' || i.price);
--DBMS_OUTPUT.put_line ('REV ID ' || i || ' = ' || i.gl_id);
END;
END LOOP;

--END;

fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
mo_global.set_policy_context ('S', v_org_id);

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
SELECT TRX_NUMBER
INTO W_TRX_Number
FROM ra_customer_trx
WHERE customer_trx_id=l_customer_trx_id;
END;

W_Status:='Success';
W_Message:='Invoice is Created-> ID = '||l_customer_trx_id;
dbms_output.put_line(W_Status);
dbms_output.put_line(W_Message);
dbms_output.put_line('Generated Oracle Txn Number-->'||W_TRX_Number);

END IF;

IF l_return_status = fnd_api.g_ret_sts_error OR
l_return_status = fnd_api.g_ret_sts_unexp_error THEN
NULL;
ELSE
SELECT count(*)
INTO l_cnt
FROM ar_trx_errors_gt;

IF l_cnt = 0 THEN
NULL;
ELSE
NULL;
FOR i in list_errors LOOP
NULL;
END LOOP;
END IF;
END IF;
COMMIT;
END;

Oracle Apps R12 api to Create AR Invoice from existing AR Invoice

DECLARE
V_OUT NUMBER;
BEGIN
XXCRT_ARINV_FRM_OLDINV(8173483,8777389);
DBMS_OUTPUT.PUT_LINE(V_OUT);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

create or replace procedure XXCRT_ARINV_FRM_OLDINV
(
P_CUSTOMER_ID NUMBER,
P_CUSTOMER_TRX_ID NUMBER
--,P_CUSTOMER_TRX_ID_OUT OUT NUMBER
)
as
--DECLARE
P_RESULT VARCHAR2 (300);
P_DATE DATE := TRUNC(SYSDATE);
l_batch_source_id NUMBER := 1003; --Source
l_cust_trx_type_id NUMBER := 1001; --type_id
v_customer_id NUMBER := P_CUSTOMER_ID;
L_CUSTOMER_TRX_ID NUMBER := P_CUSTOMER_TRX_ID;
v_user_id NUMBER := 26099; --FND_PROFILE.VALUE('USER_ID');
v_resp_id NUMBER := 50611; --FND_PROFILE.VALUE('RESP_ID');
v_resp_appl_id NUMBER := 222; --fnd_global.resp_appl_id;
v_org_id NUMBER := 1455; --FND_PROFILE.VALUE('ORG_ID');--1930;
v_date DATE := P_DATE;
v_trx_number 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_contingencies_tbl ar_invoice_api_pub.trx_contingencies_tbl_type;
trx_header_id NUMBER;
trx_line_id NUMBER;
trx_dist_id NUMBER;
v_line NUMBER := 1;
v_line_count NUMBER := 0;
v_price_total NUMBER := 0;

l_currency_code VARCHAR2 (100) := 'USD';
l_site_use_id NUMBER;
L_EXIST VARCHAR2 (100);
L_MEMO_LINE_ID NUMBER;

CURSOR cbatch IS
SELECT customer_trx_id
FROM ra_customer_trx_all
WHERE batch_id = l_batch_id;

CURSOR cvalidtxn IS
SELECT trx_header_id
FROM ar_trx_header_gt
WHERE trx_header_id NOT IN
(SELECT trx_header_id FROM ar_trx_errors_gt);

CURSOR HEADER_DETAILS IS
SELECT * FROM
RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID ;

CURSOR line_items IS
SELECT MEMO_LINE_ID,
UNIT_SELLING_PRICE PRICE,
QUANTITY_INVOICED QTY,
UOM_CODE,
(SELECT GL_ID_REV
FROM AR_MEMO_LINES_ALL_VL
WHERE MEMO_LINE_ID = RCL.MEMO_LINE_ID
AND (UOM_CODE = RCL.UOM_CODE OR RCL.UOM_CODE IS NULL)
AND ORG_ID = V_ORG_ID) GL_ID,
(SELECT DESCRIPTION
FROM AR_MEMO_LINES_ALL_VL
WHERE MEMO_LINE_ID = RCL.MEMO_LINE_ID
AND (UOM_CODE = RCL.UOM_CODE OR RCL.UOM_CODE IS NULL)
AND ORG_ID = V_ORG_ID) NAME
FROM RA_CUSTOMER_TRX_LINES_ALL RCL
WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID;

--end of cursors
BEGIN

BEGIN
SELECT ra_customer_trx_s.NEXTVAL INTO trx_header_id FROM DUAL;
END;

BEGIN

SELECT c.site_use_id
INTO l_site_use_id
FROM hz_cust_accounts a,
hz_cust_acct_sites_all b,
hz_cust_site_uses_all c,
hz_party_sites d,
hz_locations e
WHERE a.cust_account_id = b.cust_account_id
AND b.cust_acct_site_id = c.cust_acct_site_id
AND b.party_site_id = d.party_site_id
AND d.location_id = e.location_id
AND c.site_use_code = 'BILL_TO'
AND c.status = 'A'
AND b.org_id = c.org_id
AND b.org_id = v_org_id
AND a.cust_account_id = v_customer_id; --8173483

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('ERROR IN SITE--22');
END;

FOR H IN HEADER_DETAILS LOOP
fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
mo_global.init ('AR');
mo_global.set_policy_context ('S', v_org_id);
xla_security_pkg.set_security_context (v_resp_appl_id);
DBMS_OUTPUT.put_line ('l_cust_trx_type_id' || l_cust_trx_type_id);
DBMS_OUTPUT.put_line ('bill_to_customer_id' || v_customer_id);
DBMS_OUTPUT.put_line ('l_site_use_id' || l_site_use_id);
l_trx_header_tbl (1).trx_header_id := trx_header_id;
l_trx_header_tbl (1).bill_to_customer_id := v_customer_id;
l_trx_header_tbl (1).cust_trx_type_id := l_cust_trx_type_id;
l_trx_header_tbl (1).trx_date := TRUNC (p_date);
l_trx_header_tbl (1).gl_date := TRUNC (p_date);
l_trx_header_tbl (1).trx_currency := l_Currency_Code;
l_trx_header_tbl (1).primary_salesrep_id := -3;
l_trx_header_tbl (1).org_id := v_org_id;
l_trx_header_tbl (1).comments := 'Test from plsql' || ' ' || SYSDATE;
l_trx_header_tbl (1).INTERFACE_HEADER_CONTEXT := h.INTERFACE_HEADER_CONTEXT;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE1 := h.INTERFACE_HEADER_ATTRIBUTE1;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE2 := h.INTERFACE_HEADER_ATTRIBUTE2;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE3 := h.INTERFACE_HEADER_ATTRIBUTE3;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE4 := h.INTERFACE_HEADER_ATTRIBUTE4;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE5 := h.INTERFACE_HEADER_ATTRIBUTE5;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE6 :=h.INTERFACE_HEADER_ATTRIBUTE6;
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE7 :=h.INTERFACE_HEADER_ATTRIBUTE7;

l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE12 := TO_CHAR(fnd_conc_date.string_to_date(h.INTERFACE_HEADER_ATTRIBUTE13) + 1,'yyyy/mm/dd hh:mi:ss');--'2019/01/01 00:00:00';

l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE13 := TO_CHAR(ADD_MONTHS(fnd_conc_date.string_to_date(h.INTERFACE_HEADER_ATTRIBUTE13),12) ,'yyyy/mm/dd hh:mi:ss');--'2019/05/13 00:00:00';
l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE14 := h.INTERFACE_HEADER_ATTRIBUTE14;
--l_trx_header_tbl(1).attribute11:=p_Source;
--l_trx_header_tbl(1).attribute12:=p_trx_number;
--l_trx_header_tbl(1).attribute13:=TRUNC(SYSDATE);
--l_trx_header_tbl(1).attribute14:=TRUNC(P_Expiration_Date);
--l_trx_header_tbl(1).attribute15:=p_Accounting_Rule;
--l_trx_header_tbl(1).attribute10:=p_Sub_Service;
--l_trx_header_tbl(1).attribute7:=NVL(substr(p_business_name,0,60),'-');
l_trx_header_tbl (1).trx_class := 'INV';
l_trx_header_tbl (1).bill_to_site_use_id := l_site_use_id;
l_trx_header_tbl (1).term_id := 1000;
l_batch_source_rec.batch_source_id := l_batch_source_id;

END LOOP;

FOR i IN line_items
LOOP
BEGIN
v_line_count := v_line_count + v_line;
--v_price_total := v_price_total + (i.price * i.qty);
l_trx_lines_tbl (v_line_count).trx_header_id := trx_header_id;
l_trx_lines_tbl (v_line_count).trx_line_id :=
ra_customer_trx_lines_s.NEXTVAL; --trx_line_id_v;
l_trx_lines_tbl (v_line_count).line_number := v_line_count;
l_trx_lines_tbl (v_line_count).description := i.name;
l_trx_lines_tbl (v_line_count).quantity_invoiced := i.qty;
l_trx_lines_tbl (v_line_count).unit_selling_price := i.price;
l_trx_lines_tbl (v_line_count).line_type := 'LINE';
--l_trx_lines_tbl (1).tax_exempt_flag := 'E';
--l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE := 'E';
--l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE_MEANING := 'E';
l_trx_lines_tbl (v_line_count).memo_line_id := i.memo_line_id;
-- l_trx_lines_tbl (1).TAX_PRECEDENCE := 10;
-- l_trx_lines_tbl (1).TAX_RATE := 10;
l_trx_dist_tbl (v_line_count).trx_dist_id :=
ra_cust_trx_line_gl_dist_s.NEXTVAL;
l_trx_dist_tbl (v_line_count).trx_line_id :=
ra_customer_trx_lines_s.CURRVAL;
l_trx_dist_tbl (v_line_count).account_class := 'REV';
l_trx_dist_tbl (v_line_count).PERCENT := 100;
l_trx_dist_tbl (v_line_count).code_combination_id := i.gl_id;
--DBMS_OUTPUT.put_line ('i value ->' || i);
--DBMS_OUTPUT.put_line ('line- ' || i || ' Amt = ' || i.price);
--DBMS_OUTPUT.put_line ('REV ID ' || i || ' = ' || i.gl_id);
END;
END LOOP;

--END;

fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
mo_global.set_policy_context ('S', v_org_id);

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_customer_trx_id);
DBMS_OUTPUT.put_line (l_msg_data);
P_RESULT:= l_return_status||'-'||l_msg_data;
--P_CUSTOMER_TRX_ID_OUT := l_customer_trx_id;
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 ('unexpected errors found!');
ELSE
FOR cvalidtxnrec IN cvalidtxn
LOOP
IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
THEN
DBMS_OUTPUT.put_line ('Invoice(s) suceessfully created!');
DBMS_OUTPUT.put_line ( 'Batch ID: '
|| ar_invoice_api_pub.g_api_outputs.batch_id
);
l_batch_id := ar_invoice_api_pub.g_api_outputs.batch_id;
FOR cbatchrec IN cbatch
LOOP
DBMS_OUTPUT.put_line ( 'Cust Trx Id '
|| cbatchrec.customer_trx_id
);
v_trx_number := cbatchrec.customer_trx_id;
DBMS_OUTPUT.PUT_LINE('INVOICE_ID->'||v_trx_number||'-INVOICE_NUMBER->'||trx_header_id);
--P_INVOICE_ID := v_trx_number;
--P_INVOICE_NUM := cbatchrec.TRX_NUMBER;--trx_header_id_v;
/*UPDATE XX_FIA_MT
SET INVOICE_ID = v_trx_number , TAX_INVOICE_NO = trx_header_id_v
WHERE FLIGHT_REPORT_NO = P_FLIGHT_REPORT_NO;*/
END LOOP;
ELSE
DBMS_OUTPUT.put_line ('Errors found!');

END IF;
END LOOP;
END IF;
COMMIT;
END;

Oracle Apps R12 Customer Phone Number, Email and AR Invoice Details Query

SELECT
CUSTOMER_TRX_ID,SET_OF_BOOKS_ID,TRX_NUMBER,
TRX_DATE,
BILL_TO_CUSTOMER_ID,
fnd_conc_date.string_to_date(INTERFACE_HEADER_ATTRIBUTE12)ST_DT,
fnd_conc_date.string_to_date(INTERFACE_HEADER_ATTRIBUTE13)END_DT,
SUBSTR(INTERFACE_HEADER_ATTRIBUTE13,1,10) END_DATE,
INTERFACE_HEADER_ATTRIBUTE14,
RAC.ORG_ID,PARTY_NAME, ACCOUNT_NUMBER,
(select
hcp.PHONE_COUNTRY_CODE||hcp.phone_number phone_number
from apps.hz_cust_accounts hca
, apps.hz_parties hp
, apps.hz_contact_points hcp
where hca.party_id = hp.party_id
and hp.party_id = hcp.owner_table_id
and hcp.owner_table_name = 'HZ_PARTIES'
and CONTACT_POINT_PURPOSE = 'BUSINESS'
and hcp.contact_point_type = 'PHONE'
and hca.CUST_ACCOUNT_ID = BILL_TO_CUSTOMER_ID
)phone_number,
(select
hcp.EMAIL_ADDRESS
from apps.hz_cust_accounts hca
, apps.hz_parties hp
, apps.hz_contact_points hcp
where hca.party_id = hp.party_id
and hp.party_id = hcp.owner_table_id
and hcp.owner_table_name = 'HZ_PARTIES'
and CONTACT_POINT_PURPOSE = 'BUSINESS'
and hcp.contact_point_type = 'EMAIL'
and hca.CUST_ACCOUNT_ID = BILL_TO_CUSTOMER_ID)Email_Address
FROM RA_CUSTOMER_TRX_ALL RAC
,apps.hz_cust_accounts hca
, apps.hz_parties hp
WHERE
hca.CUST_ACCOUNT_ID = RAC.BILL_TO_CUSTOMER_ID
AND hca.party_id = hp.party_id
AND RAC.ORG_ID = 145
AND RAC.CUST_TRX_TYPE_ID = 1001 --type_id ???????
AND RAC.BATCH_SOURCE_ID = 1003 --Source ???????
AND RAC.INTERFACE_HEADER_ATTRIBUTE12 IS NOT NULL --ST_DT
AND RAC.INTERFACE_HEADER_ATTRIBUTE13 IS NOT NULL --END_DT
AND TRX_NUMBER = '61843'

Sunday, March 17, 2019

Oracle Apps R12 Workflow Mailer --> Remove the Actions button(Approve/Reject) from the Email

Remove the Actions button(Approve/Reject) from the Email

Solution:
Open the wtf file in workflow Builder
Right click and add attribute to the message.
Attribute: #WFM_OPEN_MAIL
Default Value: WFMAIL:OPEN_MAIL_OUTLOOK

Save and retest.

Oracle apps r12 Workflow Mailer --> EBS FYI notification closed automatically when email is fired.

EBS notification closed automatically when email is fired.

Solution:

OAM --> Notification Mailer / Edit / Advanced / Go to Step 4 (Message Generation).

Uncheck Autoclose FYI

Monday, March 11, 2019

Oracle Apps R12 api to create organization , assign organization classification

Using Cursor................

declare
l_organization_id number ;
l_object_version_number number;
l_object_version_number2 number;
l_org_information_id number;
cursor c1 is
select a org_name, b location_id,c type
from testa
;
begin
for i in c1 loop
apps.hr_organization_api.create_organization
(p_validate => NULL,
p_effective_date => sysdate,
p_language_code => apps.hr_api.userenv_lang,
p_business_group_id => 80,
p_date_from => TO_DATE ('01-JAN-1950', 'DD-MON-YYYY'),
p_name => i.org_name,
p_location_id => i.location_id,
--p_date_to => sysdate,
p_internal_external_flag => 'INT',
--p_internal_external_flag => NULL,
p_internal_address_line => NULL,
p_type => i.type,
p_comments => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_organization_id => l_organization_id,
p_object_version_number => l_object_version_number
);
dbms_output.put_line(l_organization_id);
commit;
begin
apps.hr_organization_api.create_org_classification
(p_validate => NULL,
p_effective_date => sysdate,
p_organization_id => l_organization_id,
p_org_classif_code =>'HR_ORG',
p_org_information_id => l_org_information_id,
p_object_version_number => l_object_version_number2
);
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
exception when others then
dbms_output.put_line(sqlerrm);
end;

Create single organization, assigning classification.

declare
ln_business_group_id number := 81;
l_organization_name varchar2(200):='TestAfzal_API' ;
l_organization_id number ;
l_object_version_number number;
l_org_type number := 60;
ln_location_id number := 142;
begin
apps.hr_organization_api.create_organization
(p_validate => NULL,
p_effective_date => sysdate,
p_language_code => apps.hr_api.userenv_lang,
p_business_group_id => 81,
p_date_from => TO_DATE ('01-JAN-1950', 'DD-MON-YYYY'),
p_name => l_organization_name,
p_location_id => ln_location_id,
--p_date_to => sysdate,
p_internal_external_flag => 'INT',
--p_internal_external_flag => NULL,
p_internal_address_line => NULL,
p_type => l_org_type,
p_comments => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_organization_id => l_organization_id,
p_object_version_number => l_object_version_number
);
dbms_output.put_line(l_organization_id);
exception when others then
dbms_output.put_line(sqlerrm);
end;

declare
l_organization_id number := 2031;
l_org_information_id number:= 88302;
l_org_classif_code varchar2(20) := 'HR_ORG';
l_object_version_number number;
begin
apps.hr_organization_api.create_org_classification
(p_validate => NULL,
p_effective_date => sysdate,
p_organization_id => l_organization_id,
p_org_classif_code =>l_org_classif_code,
p_org_information_id => l_org_information_id,
p_object_version_number => l_object_version_number
);
exception when others then
dbms_output.put_line(sqlerrm);
end;

select * from HR_ORGANIZATION_INFORMATION_V
where organization_id in(2030,2031)

select * from hr_all_organization_units
where organization_id in(2030,2031)

Wednesday, January 30, 2019

Oracle Apps R12 SSHR query to check final action on a transaction

SELECT ACTION FROM PQH_SS_APPROVAL_HISTORY
WHERE TRANSACTION_ITEM_KEY ='161680'
AND CREATION_DATE = (SELECT MAX(CREATION_DATE) FROM PQH_SS_APPROVAL_HISTORY
WHERE TRANSACTION_ITEM_KEY ='161680')

Query for R12 Scheduled concurrent Programs

select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31),
fnd_conc_release_classes c
where c.class_type = 'S'
and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;

Search This Blog