Oracle Apps Technical: 2024

Tuesday, September 3, 2024

Price List API

CREATE OR REPLACE PACKAGE BODY apps."XXABC_AUS_PRICE_LOAD_PKG"

/*+-------------------------------------------------------------------------+

| Project Name : XXX

| Application: Oracle Applications

| Program: XXABC_AUS_PRICE_LOAD_PKG.pkb

| Package for Price List for Australia Rollout

| Description :

| Author:

| Date: 13-May-2022

| Version: 1.0

| Modifications:

| Date Name Version Comments

|---------------------------------------------------------------------------+

| 13-May-2022 1.0 Initial Version

+---------------------------------------------------------------------------+*/

PROCEDURE price_list_load (p_errbuf OUT VARCHAR2, p_retcode OUT NUMBER)

v_return_status VARCHAR2 (1) := NULL;

v_msg_count NUMBER := 0;

v_msg_data VARCHAR2 (2000);

l_price_list_rec qp_price_list_pub.price_list_rec_type;

v_price_list_rec qp_price_list_pub.price_list_rec_type;

v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;

l_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;

v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;

v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;

v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;

v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;

l_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;

v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;

v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;

k NUMBER := 1;

j NUMBER := 1;

l_list_header_id VARCHAR2 (240) DEFAULT NULL;

l_exist_item_id NUMBER;

l_pricing_attr_id NUMBER;

l_list_line_id NUMBER;

v_uom_code VARCHAR2 (24);

v_process_flag VARCHAR2 (24);

v_customer_order_flag VARCHAR2 (24);

l_user_id NUMBER := fnd_global.user_id;

l_resp_id NUMBER := fnd_global.resp_id;

l_resp_appl_id NUMBER := fnd_global.resp_appl_id;

v_inventory_item_id NUMBER;

v_status VARCHAR2 (10);

v_err_msg VARCHAR2 (2000);

l_rec_cnt NUMBER := 0;

l_rec_passed NUMBER := 0;

l_rec_falied NUMBER := 0;

CURSOR cur_price_list

SELECT ROWID, TRIM (conversion_site_code) conversion_site_code,

TRIM (conversion_plant_code) conversion_plant_code,

TRIM (legacy_identifier) legacy_identifier,

TRIM (target_destination) target_destination,

TRIM (legacy_item_number) legacy_item_number,

TRIM (uom_code) uom_code,

TRIM (price_list_name) price_list_name,

TRIM (price_currency) price_currency,

TRIM (price_list_price) price_list_price,

TRIM (effective_start_date) effective_start_date,

TRIM (effective_end_date) effective_end_date,

TRIM (product_attribute) product_attribute,

TRIM (product_attribute_context) product_attribute_context,

TRIM (status) status, TRIM (error_message) error_message

FROM xxcust.XXABC_au_price_stg

WHERE NVL (status, 'X') <> 'P';

BEGIN

-- INITIALIZATION REQUIRED FOR R12

mo_global.set_policy_context ('S', fnd_profile.VALUE ('ORG_ID'));

mo_global.init ('ONT');

fnd_global.apps_initialize (user_id => l_user_id,

resp_id => l_resp_id,

resp_appl_id => l_resp_appl_id

);

-------------------------------------------------------------------------------------

-- Update the flag to NULL before processing error records

BEGIN

UPDATE xxcust.XXABC_au_price_stg

SET error_message = NULL,

process_flag = NULL

WHERE status = 'E';

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'Error in updating staging table'

);

END;

FOR i_rec IN cur_price_list

LOOP

v_inventory_item_id := NULL;

l_list_header_id := NULL;

l_exist_item_id := NULL;

l_pricing_attr_id := NULL;

l_list_line_id := NULL;

v_uom_code := 'EA'; -- reset to EA if not find

v_process_flag := NULL;

v_customer_order_flag := NULL;

v_status := 'P'; -- reset to P every time

v_err_msg := NULL; -- reset to NULL

-- Check if item exists in validation org to assign price list

BEGIN

SELECT inventory_item_id, primary_uom_code,

msib.customer_order_flag

INTO v_inventory_item_id, v_uom_code,

v_customer_order_flag

FROM mtl_system_items_b msib, org_organization_definitions ood

WHERE TRIM (segment1) = TRIM (i_rec.legacy_item_number)

AND ood.organization_id = msib.organization_id

AND ood.organization_code = '590';

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Item does not exists in validation org --> '

|| i_rec.legacy_item_number

);

v_status := 'E';

v_err_msg :=

'Item does not exists in validation org --> '

|| i_rec.legacy_item_number;

END;

IF v_customer_order_flag = 'N'

THEN

fnd_file.put_line

(fnd_file.LOG,

'Customer order checkbox is not checked for item in validation org --> '

|| i_rec.legacy_item_number

);

v_status := 'E';

v_err_msg :=

'Customer order checkbox is not checked for item in validation org --> '

|| i_rec.legacy_item_number;

END IF;

------------------------------------------------------------------

-- Price list alredy exist or not in XXX

BEGIN

SELECT DISTINCT qlh.list_header_id

INTO l_list_header_id

FROM apps.qp_list_headers qlh

WHERE qlh.NAME = TRIM (i_rec.price_list_name);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Exception while deriving the price list name -'

|| i_rec.price_list_name

);

v_status := 'E';

v_err_msg :=

v_err_msg

|| '/Exception while deriving the price list name -'

|| i_rec.price_list_name;

END;

IF v_status = 'P'

THEN

--------------------------------------------------------------------------------

-- Item already exist on price list check

BEGIN

SELECT qpp.product_attr_value, qpp.pricing_attribute_id,

qll.list_line_id

INTO l_exist_item_id, l_pricing_attr_id,

l_list_line_id

FROM apps.qp_list_headers qlh,

apps.qp_list_lines qll,

apps.qp_pricing_attributes qpp

WHERE qlh.list_header_id = qll.list_header_id

AND qll.list_line_id = qpp.list_line_id

AND qpp.list_header_id = qlh.list_header_id

AND qlh.NAME = TRIM (i_rec.price_list_name)

AND qpp.product_attr_value = TRIM (v_inventory_item_id);

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

-----------------------------------------------------------------------------

IF l_exist_item_id IS NOT NULL

THEN

v_process_flag := 'UPDATE';

k := 1; -- UPDATE the price list line rec

l_price_list_line_tbl (k).list_header_id := l_list_header_id;

l_price_list_line_tbl (k).list_line_id := l_list_line_id;

l_price_list_line_tbl (k).list_line_type_code := 'PLL';

l_price_list_line_tbl (k).operation := qp_globals.g_opr_update;

l_price_list_line_tbl (k).operand := i_rec.price_list_price;

l_price_list_line_tbl (k).start_date_active := SYSDATE;

l_price_list_line_tbl (k).arithmetic_operator := 'UNIT_PRICE';

j := 1;

l_pricing_attr_tbl (j).pricing_attribute_id :=

l_pricing_attr_id;

l_pricing_attr_tbl (j).list_line_id := l_list_line_id;

l_pricing_attr_tbl (j).product_attribute_context := 'ITEM';

l_pricing_attr_tbl (j).product_attribute :=

'PRICING_ATTRIBUTE1';

l_pricing_attr_tbl (j).product_attr_value :=

v_inventory_item_id;

l_pricing_attr_tbl (j).product_uom_code := v_uom_code;

l_pricing_attr_tbl (j).excluder_flag := 'N';

l_pricing_attr_tbl (j).attribute_grouping_no := 1;

l_pricing_attr_tbl (j).price_list_line_index := 1;

l_pricing_attr_tbl (j).operation := qp_globals.g_opr_update;

ELSE

v_process_flag := 'INSERT';

k := 1; -- create the price list line rec

l_price_list_line_tbl (k).list_header_id := l_list_header_id;

l_price_list_line_tbl (k).list_line_id :=

qp_list_lines_s.NEXTVAL;

l_price_list_line_tbl (k).list_line_type_code := 'PLL';

l_price_list_line_tbl (k).operation := qp_globals.g_opr_create;

l_price_list_line_tbl (k).operand := i_rec.price_list_price;

l_price_list_line_tbl (k).start_date_active := SYSDATE;

l_price_list_line_tbl (k).arithmetic_operator := 'UNIT_PRICE';

j := 1; -- create the price list attributes rec

l_pricing_attr_tbl (j).pricing_attribute_id :=

qp_pricing_attributes_s.NEXTVAL;

l_pricing_attr_tbl (j).list_line_id := qp_list_lines_s.CURRVAL;

l_pricing_attr_tbl (j).product_attribute_context := 'ITEM';

l_pricing_attr_tbl (j).product_attribute :=

'PRICING_ATTRIBUTE1';

l_pricing_attr_tbl (j).product_attr_value :=

v_inventory_item_id;

l_pricing_attr_tbl (j).product_uom_code := v_uom_code;

l_pricing_attr_tbl (j).excluder_flag := 'N';

l_pricing_attr_tbl (j).attribute_grouping_no := 1;

l_pricing_attr_tbl (j).price_list_line_index := 1;

l_pricing_attr_tbl (j).operation := qp_globals.g_opr_create;

END IF;

qp_price_list_pub.process_price_list

(p_api_version_number => 1,

p_init_msg_list => fnd_api.g_false,

p_return_values => fnd_api.g_false,

p_commit => fnd_api.g_false,

x_return_status => v_return_status,

x_msg_count => v_msg_count,

x_msg_data => v_msg_data,

p_price_list_rec => l_price_list_rec,

p_price_list_line_tbl => l_price_list_line_tbl,

p_pricing_attr_tbl => l_pricing_attr_tbl,

x_price_list_rec => v_price_list_rec,

x_price_list_val_rec => v_price_list_val_rec,

x_price_list_line_tbl => v_price_list_line_tbl,

x_price_list_line_val_tbl => v_price_list_line_val_tbl,

x_qualifiers_tbl => v_qualifiers_tbl,

x_qualifiers_val_tbl => v_qualifiers_val_tbl,

x_pricing_attr_tbl => v_pricing_attr_tbl,

x_pricing_attr_val_tbl => v_pricing_attr_val_tbl

);

IF v_return_status = fnd_api.g_ret_sts_success

THEN

v_status := 'P';

v_err_msg :=

'The Item has been successfully loaded into the price list --> '

|| i_rec.legacy_item_number;

ELSE

ROLLBACK;

v_msg_data := NULL;

FOR k IN 1 .. v_msg_count

LOOP

v_msg_data :=

oe_msg_pub.get (p_msg_index => k,

p_encoded => 'F');

v_msg_data := v_msg_data || v_msg_data;

END LOOP;

v_status := 'E';

v_err_msg :=

'The Error Message Due to which The Item has not been loaded to Price List is --> '

|| v_msg_data;

END IF;

IF v_status = 'P'

THEN

UPDATE xxcust.XXABC_au_price_stg

SET status = v_status,

error_message = v_err_msg,

process_flag = v_process_flag

WHERE ROWID = i_rec.ROWID;

COMMIT;

l_rec_cnt := l_rec_cnt + 1;

ELSIF v_status = 'E'

THEN

UPDATE xxcust.XXABC_au_price_stg

SET status = v_status,

error_message = v_err_msg,

process_flag = v_process_flag

WHERE ROWID = i_rec.ROWID;

l_rec_falied := l_rec_falied + 1;

COMMIT;

END IF;

l_rec_passed := l_rec_passed + 1;

END LOOP;

fnd_file.put_line (fnd_file.LOG,

'Total Records passed in the program =-> '

|| l_rec_passed

);

fnd_file.put_line (fnd_file.LOG,

'Total Records Inserted successfully =-> '

|| l_rec_cnt

);

fnd_file.put_line (fnd_file.LOG,

'Total Records Failed =-> '

|| l_rec_falied

);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'Exception while loading the price list :- '

|| SQLERRM

);

END price_list_load;

END XXABC_aus_price_load_pkg;

Tuesday, July 16, 2024

Create table using function ( along with procedure) - Example 2 - ar_get_customer_balance_pkg.ar_get_customer_balance

--Step 1

--===========================================

create or replace type xxhf_cobl as object (

REQUEST_ID NUMBER,

AS_OF_DATE DATE,

ORGANIZATION_NAME VARCHAR2 (60),

FUNCTIONAL_CURRENCY_CODE VARCHAR2 (15),

CUSTOMER_NAME VARCHAR2 (360),

CUSTOMER_NUMBER VARCHAR2 (30),

TAX_REFERENCE_NUM VARCHAR2 (50),

ADDRESS_LINE1 VARCHAR2 (240),

ADDRESS_LINE2 VARCHAR2 (240),

ADDRESS_LINE3 VARCHAR2 (240),

ADDRESS_LINE4 VARCHAR2 (240),

CITY VARCHAR2 (60),

STATE VARCHAR2 (60),

ZIP VARCHAR2 (60),

COUNTRY VARCHAR2 (60),

TRANS_TYPE VARCHAR2 (20),

TRX_NUMBER VARCHAR2 (30),

TRANSACTION_DATE DATE,

TRX_CURRENCY_CODE VARCHAR2 (15),

TRANS_AMOUNT NUMBER,

TRANS_AMOUNT_REMAINING NUMBER,

RECEIPT_AMOUNT NUMBER,

ADJUSTMENT_AMOUNT NUMBER,

EARNED_DISCOUNT_AMOUNT NUMBER,

UNEARNED_DISCOUNT_AMOUNT NUMBER,

INVOICE_CREDIT_AMOUNT NUMBER,

BANK_CHARGE NUMBER,

ON_ACCOUNT_CREDIT_AMOUNT NUMBER,

ON_ACCOUNT_RECEIPTS NUMBER,

UNAPPLIED_RECEIPTS NUMBER

);

--Step 2

--===========================================

create or replace type xxhf_cobl_nested_table as table of xxhf_cobl;

--Step 3

--===========================================

CREATE OR REPLACE FUNCTION xxhf_cust_open_bal_letter

RETURN xxhf_cobl_nested_table

v_ret xxhf_cobl_nested_table;

p_request_id NUMBER;

p_set_of_books_id NUMBER;

p_as_of_date DATE;

p_customer_name_from VARCHAR2 (200);

p_customer_name_to VARCHAR2 (200);

p_customer_number_low VARCHAR2 (200);

p_customer_number_high VARCHAR2 (200);

p_currency VARCHAR2 (200);

p_min_invoice_balance NUMBER;

p_min_open_balance NUMBER;

p_account_credits VARCHAR2 (200);

p_account_receipts VARCHAR2 (200);

p_unapp_receipts VARCHAR2 (200);

p_uncleared_receipts VARCHAR2 (200);

p_ref_no VARCHAR2 (200);

p_debug_flag VARCHAR2 (200);

p_trace_flag VARCHAR2 (200);

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

v_ret := xxhf_cobl_nested_table ();

p_request_id := 2;

p_set_of_books_id := NULL;

p_as_of_date := SYSDATE;

p_customer_name_from := NULL;

p_customer_name_to := NULL;

p_customer_number_low := NULL;

p_customer_number_high := NULL;

p_currency := NULL;

p_min_invoice_balance := NULL;

p_min_open_balance := NULL;

p_account_credits := NULL;

p_account_receipts := NULL;

p_unapp_receipts := NULL;

p_uncleared_receipts := NULL;

p_ref_no := NULL;

p_debug_flag := 'N';

p_trace_flag := 'N';

mo_global.init ('AR');

DELETE FROM ar_customer_balance_itf

WHERE request_id = 2;

COMMIT;

apps.ar_get_customer_balance_pkg.ar_get_customer_balance

(p_request_id,

p_set_of_books_id,

p_as_of_date,

p_customer_name_from,

p_customer_name_to,

p_customer_number_low,

p_customer_number_high,

p_currency,

p_min_invoice_balance,

p_min_open_balance,

p_account_credits,

p_account_receipts,

p_unapp_receipts,

p_uncleared_receipts,

p_ref_no,

p_debug_flag,

p_trace_flag

);

COMMIT;

SELECT xxhf_cobl (request_id,

as_of_date,

organization_name,

functional_currency_code,

customer_name,

customer_number,

tax_reference_num,

address_line1,

address_line2,

address_line3,

address_line4,

city,

state,

zip,

country,

trans_type,

trx_number,

transaction_date,

trx_currency_code,

trans_amount,

trans_amount_remaining,

receipt_amount,

adjustment_amount,

earned_discount_amount,

unearned_discount_amount,

invoice_credit_amount,

bank_charge,

on_account_credit_amount,

on_account_receipts,

unapplied_receipts

)

BULK COLLECT INTO v_ret

FROM ar_customer_balance_itf

WHERE 1 = 1 AND request_id = 2;

RETURN v_ret;

END xxhf_cust_open_bal_letter;

--Step 4

--===========================================

CREATE OR REPLACE VIEW xxhf_cust_open_bal_letter_v

SELECT organization_name, as_of_date, customer_name, customer_number,

tax_reference_num, address_line1, address_line2, address_line3,

address_line4, city, state, zip, country,

DECODE (trans_type,

'INV', 'Standard',

'DM', 'Debit Memo',

'CB', 'Chargeback',

'DEP', 'Deposit'

) trans_type,

trx_number, transaction_date, trx_currency_code,

NVL (trans_amount, 0) trans_amount,

NVL (trans_amount_remaining, 0) trans_amount_remaining,

NVL (receipt_amount, 0) receipt_amount,

NVL (adjustment_amount, 0) adjustment_amount,

NVL (earned_discount_amount, 0) earned_discount_amount,

NVL (unearned_discount_amount, 0) unearned_discount_amount,

NVL (invoice_credit_amount, 0) invoice_credit_amount,

NVL (bank_charge, 0) bank_charge,

NVL (on_account_credit_amount, 0) on_account_credit_amount,

NVL (on_account_receipts, 0) on_account_receipts,

NVL (unapplied_receipts, 0) unapplied_receipts

--nvl(uncleared_receipts,0) uncleared_receipts

FROM TABLE (xxhf_cust_open_bal_letter)

WHERE request_id = 2

ORDER BY transaction_date, trx_number

Create table using function ( along with procedure) - Example 1

--Step 1

--===========================================

create or replace type xxhf_aptb_obj as object (

LEDGER VARCHAR2 (50)

,INVOICE_NUM VARCHAR2 (150)

,INVOICE_DATE DATE

,INV_CURR VARCHAR2 (50)

,INV_AMOUNT NUMBER

,INV_REM_AMT NUMBER

,SEGMENT1 VARCHAR2 (100)

,VENDOR_NAME VARCHAR2 (360)

,CONCATENATED_SEGMENTS VARCHAR2 (240)

,REMAINING_AMOUNT NUMBER

,INVOICE_AMOUNT NUMBER

,PAYMENT_STATUS VARCHAR2 (140)

,SOURCE_ENTITY_ID NUMBER

,LEDGER_ID NUMBER (15)

,CODE_COMBINATION_ID NUMBER

,INVOICE_ID NUMBER (15)

,ORG_ID NUMBER (15)

);

--Step 2

--===========================================

create or replace type xxhf_aptb_nested_table as table of xxhf_aptb_obj;

--Step 3

--===========================================

CREATE OR REPLACE FUNCTION xxhf_ap_trial_bal_rpt

RETURN xxhf_aptb_nested_table

v_ret xxhf_aptb_nested_table;

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

mo_global.init ('SQLAP');

SELECT xxhf_aptb_obj (ledger,

invoice_num,

invoice_date,

inv_curr,

inv_amount,

inv_rem_amt,

segment1,

vendor_name,

concatenated_segments,

remaining_amount,

invoice_amount,

payment_status,

source_entity_id,

ledger_id,

code_combination_id,

invoice_id,

org_id

)

BULK COLLECT INTO v_ret

FROM (SELECT gl.NAME ledger, aia.invoice_num, aia.invoice_date,

aia.invoice_currency_code inv_curr,

aia.invoice_amount inv_amount,

SUM

(CASE

WHEN aia.invoice_currency_code =

aia.payment_currency_code

THEN (NVL (tb.entered_rounded_cr, 0))

- (NVL (tb.entered_rounded_dr, 0))

ELSE ( NVL (tb.entered_rounded_cr, 0)

- (( NVL (tb.entered_rounded_dr, 0)

* NVL

((SELECT conversion_rate

FROM gl_daily_rates

WHERE conversion_type =

aia.exchange_rate_type

AND TRUNC (conversion_date) =

TRUNC

(NVL

(aia.exchange_date,

tb.gl_date

)

AND from_currency =

aia.invoice_currency_code

AND to_currency =

aia.payment_currency_code),

)

END

) inv_rem_amt,

(SELECT segment1

FROM ap_suppliers

WHERE vendor_id = aia.vendor_id) segment1,

hp.party_name vendor_name, gcc.concatenated_segments,

SUM (NVL (tb.acctd_rounded_cr, 0))

- SUM (NVL (tb.acctd_rounded_dr, 0)) remaining_amount,

SUM

(CASE

WHEN tb.applied_to_entity_id IS NULL

THEN (NVL (tb.acctd_rounded_cr, 0))

- (NVL (tb.acctd_rounded_dr, 0))

ELSE 0

END

) invoice_amount,

flv_paystatus.meaning payment_status,

NVL (tb.applied_to_entity_id,

tb.source_entity_id

) source_entity_id,

tb.ledger_id, tb.code_combination_id, aia.invoice_id,

aia.org_id

FROM xla_transaction_entities xte,

xla_trial_balances tb,

ap_invoices_all aia,

hz_parties hp,

gl_code_combinations_kfv gcc,

gl_ledgers gl,

fnd_lookup_values flv_paystatus

WHERE 1 = 1

AND gl.ledger_id = tb.ledger_id

AND aia.party_id = hp.party_id

AND NVL (tb.applied_to_entity_id, tb.source_entity_id) =

xte.entity_id

AND xte.ledger_id = tb.ledger_id

AND xte.application_id = 200

AND tb.source_application_id = 200

AND aia.set_of_books_id = xte.ledger_id

AND aia.invoice_id = xte.source_id_int_1

AND tb.code_combination_id = gcc.code_combination_id

----lp_date

AND flv_paystatus.lookup_code = aia.payment_status_flag

AND flv_paystatus.LANGUAGE = 'US'

AND NVL (flv_paystatus.end_date_active, SYSDATE + 1) > SYSDATE

AND flv_paystatus.lookup_type = 'INVOICE PAYMENT STATUS'

AND TRUNC (tb.gl_date) <= TRUNC (SYSDATE)

GROUP BY aia.invoice_num,

aia.invoice_date,

aia.invoice_currency_code,

aia.invoice_amount,

( aia.invoice_amount

- ( NVL (aia.amount_paid, 0)

+ NVL (aia.discount_amount_taken, 0)

)

aia.vendor_id,

hp.party_name,

gcc.concatenated_segments,

aia.invoice_amount,

NVL (tb.applied_to_entity_id, tb.source_entity_id),

tb.ledger_id,

tb.code_combination_id,

aia.invoice_id,

aia.org_id,

gl.NAME,

flv_paystatus.meaning

HAVING SUM (NVL (tb.acctd_rounded_cr, 0)) <>

SUM (NVL (tb.acctd_rounded_dr, 0)));

RETURN v_ret;

END xxhf_ap_trial_bal_rpt;

--Step 4

--===========================================

CREATE OR REPLACE VIEW xxhf_ap_trial_bal_rpt_v

as select * from TABLE (xxhf_ap_trial_bal_rpt) order by 1

Monday, July 15, 2024

AP Trial Balance Report query

SELECT

aia.invoice_num,

aia.invoice_date,

aia.invoice_currency_code inv_curr,

aia.invoice_amount inv_amount,

SUM(CASE WHEN aia.invoice_currency_code = aia.payment_currency_code THEN

(NVL(tb.entered_rounded_cr,0)) - (NVL(tb.entered_rounded_dr,0))

ELSE (NVL(tb.entered_rounded_cr,0) - ((NVL(tb.entered_rounded_dr,0)*NVL((select conversion_rate

from gl_daily_rates

where conversion_type = aia.exchange_rate_type

and trunc(conversion_date) = trunc(nvl(aia.exchange_date, tb.gl_date))

and from_currency = aia.invoice_currency_code

and to_currency = aia.payment_currency_code),0))))

END) inv_rem_amt,

(select segment1 from ap_suppliers where vendor_id = aia.vendor_id) segment1,

hp.party_name vendor_name,

gcc.concatenated_segments,

SUM (nvl(tb.acctd_rounded_cr,0)) - SUM (nvl(tb.acctd_rounded_dr,0)) remaining_amount,

SUM(CASE WHEN tb.applied_to_entity_id IS NULL THEN

(NVL(tb.acctd_rounded_cr,0)) - (NVL(tb.acctd_rounded_dr,0))

ELSE 0

END) invoice_amount,

NVL (tb.applied_to_entity_id, tb.source_entity_id) source_entity_id,

tb.ledger_id ,

tb.code_combination_id,

aia.invoice_id ,

aia.org_id

FROM xla_transaction_entities xte

, xla_trial_balances tb

, ap_invoices aia

, hz_parties hp

, gl_code_combinations_kfv gcc

WHERE 1=1

AND aia.party_id = hp.party_id

AND NVL (tb.applied_to_entity_id, tb.source_entity_id) = xte.entity_id

AND xte.ledger_id= tb.ledger_id

AND xte.application_id = 200

and tb.source_application_id = 200

AND aia.set_of_books_id = xte.ledger_id

AND aia.invoice_id = xte.source_id_int_1

AND tb.code_combination_id = gcc.code_combination_id

----lp_date

and trunc(tb.gl_date) <=trunc(sysdate)

GROUP BY aia.invoice_num,

aia.invoice_date,

aia.invoice_currency_code,

aia.invoice_amount,

(aia.invoice_amount-(nvl(aia.amount_paid,0)+nvl(aia.discount_amount_taken,0))),

aia.vendor_id,

hp.party_name,

gcc.concatenated_segments,

aia.invoice_amount,

NVL (tb.applied_to_entity_id, tb.source_entity_id),

tb.ledger_id,

tb.code_combination_id,

aia.invoice_id,

aia.org_id

HAVING SUM (nvl(tb.acctd_rounded_cr,0)) <> SUM (nvl(tb.acctd_rounded_dr,0))

begin

mo_global.set_policy_context('S',29830);

end;

Tuesday, April 30, 2024

Apps Initialize / fnd log procedures

CREATE OR REPLACE PROCEDURE APPS."XXX_LOG" (pmessage VARCHAR2)

BEGIN

-- This Procedure is used to write on Log file

DBMS_OUTPUT.put_line (pmessage);

fnd_file.put_line (fnd_file.LOG, pmessage);

EXCEPTION

WHEN OTHERS

THEN

NULL;

END XXX_LOG;

PROCEDURE apps_initialize (p_org_id IN NUMBER)

v_user_id NUMBER := fnd_profile.VALUE ('USER_ID');

v_resp_id NUMBER := fnd_profile.VALUE ('RESP_ID');

v_resp_appl_id NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');

lv_request_id NUMBER;

lv_org_id NUMBER := fnd_profile.VALUE ('ORG_ID');

BEGIN

lv_org_id := p_org_id;

IF (lv_org_id = 777)

THEN

v_user_id := 888;

v_resp_id := 53223;

v_resp_appl_id := 200;

ELSIF (lv_org_id = 9874)

THEN

v_user_id := 99;

v_resp_id := 53345;

v_resp_appl_id := 200;

ELSIF (lv_org_id = 3657)

THEN

v_user_id := 666;

v_resp_id := 53885;

v_resp_appl_id := 200;

ELSE

v_user_id := 33;

v_resp_id := 50645;

v_resp_appl_id := 200;

END IF;

fnd_global.apps_initialize (user_id => v_user_id,

resp_id => v_resp_id,

resp_appl_id => v_resp_appl_id);

mo_global.init ('SQLAP');

fnd_client_info.set_org_context (lv_org_id);

END apps_initialize;

Wednesday, February 7, 2024

Adding Resp Via API / Deleting Executable and concurrent Program

DECLARE

lv_user_name VARCHAR2 (20) := 'SENTHIL';

lv_req_resp_key VARCHAR2 (50); -- := 'APPLICATION_DEVELOPER';

lv_description VARCHAR2 (100) := 'Via script added';

lv_req_resp_name VARCHAR2 (200);

lv_appl_shrt_name VARCHAR2 (20);

lv_appl_name VARCHAR2 (50);

lv_resp_key VARCHAR2 (50);

cursor c1 is

SELECT fav.application_short_name,

fav.application_name,

upper(frv.responsibility_name) responsibility_name,

frv.responsibility_key

--INTO lv_appl_shrt_name, lv_appl_name, lv_req_resp_name

FROM fnd_application_vl fav, fnd_responsibility_vl frv

WHERE frv.application_id = fav.application_id

and upper(frv.responsibility_name) in

(

'FUNCTIONAL ADMINISTRATOR'

,'AP PAYABLES SUPER USER'

);

BEGIN

DBMS_OUTPUT.put_line ('Below Responsibility Added for this user '|| lv_user_name);

DBMS_OUTPUT.put_line ('--------------------------------------------------------------');

for rec in c1

loop

/*SELECT fav.application_short_name,

fav.application_name,

frv.responsibility_name

INTO lv_appl_shrt_name, lv_appl_name, lv_req_resp_name

FROM fnd_application_vl fav, fnd_responsibility_vl frv

WHERE frv.application_id = fav.application_id

AND frv.responsibility_key = lv_req_resp_key; */

fnd_user_pkg.addresp (username => lv_user_name,

resp_app => rec.application_short_name, --lv_appl_shrt_name,

resp_key => rec.responsibility_key , --lv_req_resp_key,

security_group => 'STANDARD',

description => lv_description,

start_date => SYSDATE,

end_date => NULL

);

COMMIT;

DBMS_OUTPUT.put_line (rec.responsibility_name);

end loop;

DBMS_OUTPUT.put_line ('--------------------------------------------------------------');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line ('Responsibility IS NOT added due to ' || SQLCODE || '; ' || SUBSTR (SQLERRM, 1, 250));

ROLLBACK;

END;

/* Deleting Executable and concurrent Program*/

SET SERVEROUTPUT ON;
 
DECLARE
   l_prog_short_name   VARCHAR2 (240);
   l_exec_short_name   VARCHAR2 (240);
   l_appl_full_name    VARCHAR2 (240);
   l_appl_short_name   VARCHAR2 (240);
   l_del_prog_flag     VARCHAR2 (1) := 'Y'; --Set flag whether to delete Concurrent program or not
   l_del_exec_flag     VARCHAR2 (1) := 'Y'; --Set flag whether to delete executable or not
BEGIN
   --
   -- set concurrent program and executable short name
   --
   l_prog_short_name := 'XX_TEST_CP';             -- Concurrent program short name
   l_exec_short_name := 'XX_TEST_EXEC';           -- Executable short name
   l_appl_full_name := 'XX Custom Application';   -- Application full name
   l_appl_short_name := 'XXCUST';                 -- Application Short name
 
   --
   -- Check if the program exists. if found, delete the program
   --
   IF     fnd_program.program_exists (l_prog_short_name, l_appl_short_name)
      AND fnd_program.executable_exists (l_exec_short_name, l_appl_short_name)
   THEN
 
      IF l_del_prog_flag = 'Y'
	  THEN
      --
      --API call to delete Concurrent Program
      --
      fnd_program.delete_program (l_prog_short_name, l_appl_full_name);
	  --
	  END IF;
	  --
      IF l_del_exec_flag = 'Y'
	  THEN
      --
      --API call to delete Executable
      --
      fnd_program.delete_executable (l_exec_short_name, l_appl_full_name);
	  --
	  END IF;
      COMMIT;
	  --
      DBMS_OUTPUT.put_line ('Concurrent Program '||l_prog_short_name || ' deleted successfully');
      DBMS_OUTPUT.put_line ('Executable '||l_exec_short_name || ' deleted successfully');
   --
   -- if the program does not exist in the system
   --
   ELSE
      DBMS_OUTPUT.put_line (l_prog_short_name ||' or '||l_exec_short_name|| ' not found');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error while deleting: ' || SQLERRM);
END;

/* Delete XML Publisher Definition and Template*/

SET SERVEROUTPUT ON

DECLARE

-- Change the following two parameters

var_templateCode VARCHAR2 (100) := 'XX_US_BEN_NOT_EXP_ELEC'; -- Template Code

boo_deleteDataDef BOOLEAN := TRUE; -- delete the associated Data Def.

BEGIN

FOR RS

IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,

T1.DATA_SOURCE_CODE,

T2.APPLICATION_SHORT_NAME DEF_APP_NAME

FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2

WHERE T1.TEMPLATE_CODE = var_templateCode

AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)

LOOP

XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

DELETE FROM XDO_LOBS

WHERE LOB_CODE = var_templateCode

AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME

AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

DELETE FROM XDO_CONFIG_VALUES

WHERE APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME

AND TEMPLATE_CODE = var_templateCode

AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE

AND CONFIG_LEVEL = 50;

DBMS_OUTPUT.PUT_LINE ('Template ' || var_templateCode || ' deleted.');

IF boo_deleteDataDef

THEN

XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,

RS.DATA_SOURCE_CODE);

DELETE FROM XDO_LOBS

WHERE LOB_CODE = RS.DATA_SOURCE_CODE

AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME

AND LOB_TYPE IN

('XML_SCHEMA',

'DATA_TEMPLATE',

'XML_SAMPLE',

'BURSTING_FILE');

DELETE FROM XDO_CONFIG_VALUES

WHERE APPLICATION_SHORT_NAME = RS.DEF_APP_NAME

AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE

AND CONFIG_LEVEL = 30;

DBMS_OUTPUT.PUT_LINE (

'Data Defintion ' || RS.DATA_SOURCE_CODE || ' deleted.');

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE (

'Issue a COMMIT to make the changes or ROLLBACK to revert.');

EXCEPTION

WHEN OTHERS

THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE (

'Unable to delete XML Publisher Template ' || var_templateCode);

DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));

END;

Query to find Responsibility for the Concurrent Program

SELECT distinct frt.responsibility_name,

frg.request_group_name,

frg.description,fcpt.user_concurrent_program_name

FROM fnd_request_groups frg

,fnd_request_group_units frgu

,fnd_concurrent_programs fcp

,fnd_concurrent_programs_tl fcpt

,fnd_responsibility_tl frt

,fnd_responsibility frs

WHERE frgu.unit_application_id = fcp.application_id

AND frgu.request_unit_id = fcp.concurrent_program_id

AND frg.request_group_id = frgu.request_group_id

AND frg.application_id = frgu.application_id

AND fcpt.source_lang = USERENV('LANG')

AND fcp.application_id = fcpt.application_id

AND fcp.concurrent_program_id = fcpt.concurrent_program_id

AND frs.application_id = frt.application_id

AND frs.responsibility_id = frt.responsibility_id

AND frt.source_lang = USERENV('LANG')

AND frs.request_group_id = frg.request_group_id

AND frs.application_id = frg.application_id

--AND fcp.concurrent_program_name = <shortname>

AND fcpt.user_concurrent_program_name LIKE '%XXX%Pre%%%'

and frt.responsibility_name like '%XX_PO%'