How to assign Operating Unit to Responsibility

Navigation: System Administrator à Profile à System

By using "MO: Operating Unit" profile we can assign org id to responsibility

Query to find Responsibility Name from Org id

SELECT 
         frv.responsibility_name,
         fpov.profile_option_value org_id,
         hou.NAME
    FROM
         apps.fnd_profile_options_vl fpo,
         apps.fnd_responsibility_vl frv,
         apps.fnd_profile_option_values fpov,
         apps.hr_organization_units hou
   WHERE  hou.organization_id =204
     AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
     AND fpo.profile_option_id = fpov.profile_option_id
     AND fpo.user_profile_option_name = 'MO: Operating Unit'
     AND frv.responsibility_id = fpov.level_value
ORDER BY frv.responsibility_name;

Query to find Org id from Responsibility Name

 SELECT 
         frv.responsibility_name,
         hou.NAME,
         fpov.profile_option_value org_id
    FROM apps.hr_organization_units hou,
         apps.fnd_profile_options_vl fpo,
         apps.fnd_profile_option_values fpov,
         apps.fnd_responsibility_vl frv
   WHERE frv.responsibility_name = 'General Ledger Vision'
     AND fpov.level_value = frv.responsibility_id
     AND fpo.profile_option_id = fpov.profile_option_id
     AND fpo.user_profile_option_name = 'MO: Operating Unit'
     AND fpov.profile_option_id = fpo.profile_option_id
     AND hou.organization_id = TO_NUMBER (fpov.profile_option_value)
ORDER BY frv.responsibility_name

Query to convert system date to specific date a time zone

 SELECT
    TO_CHAR( (from_tz(to_timestamp(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),'CST') AT TIME ZONE 'Asia/Calcutta'),'DD-MON-YYYY HH24:MI:SS') DATE_TIME
FROM
    dual

How to Enable Personalization link for OAF Pages

Profile Name	Value
FND: Personalization Region Link Enabled	Yes
Personalize Self-Service Defn	Yes
Disable Self-Service Personal	No

AR invoice adjustment api script ( ar_adjust_pub.create_adjustment)

SET SERVEROUTPUT ON

DECLARE
    v_init_msg_list         VARCHAR2(1000);
    v_commit_flag           VARCHAR2(5) := 'F';
    v_validation_level      NUMBER(4) := fnd_api.g_valid_level_full;
    v_msg_count             NUMBER(4);
    v_msg_data              VARCHAR2(1000);
    v_return_status         VARCHAR2(5);
    v_adj_rec               ar_adjustments%rowtype;
    v_chk_approval_limits   VARCHAR2(5) := 'F';
    v_check_amount          VARCHAR2(5) := 'F';
    v_move_deferred_tax     VARCHAR2(1) := 'Y';
    v_new_adjust_number     ar_adjustments.adjustment_number%TYPE;
    v_new_adjust_id         ar_adjustments.adjustment_id%TYPE;
    v_called_from           VARCHAR2(25) := 'ADJ-API';
    v_old_adjust_id         ar_adjustments.adjustment_id%TYPE;
    l_customer_trx_id       NUMBER;
    l_payment_schedule_id   NUMBER;
    l_receivables_trx_id    NUMBER;
    l_first_char            VARCHAR2(150);
    l_second_char           VARCHAR2(150);

BEGIN
    dbms_output.put_line('Program Start');
    BEGIN
        SELECT
            a.customer_trx_id,
            b.payment_schedule_id,
            substr(interface_header_attribute2,1,instr(interface_header_attribute2,'.') - 1),
            TRIM(regexp_substr(interface_header_attribute2,'(\S*)(\s)',1,2) )
        INTO
            l_customer_trx_id,
            l_payment_schedule_id,
            l_first_char,
            l_second_char
        FROM
            ra_customer_trx_all a,
            ar_payment_schedules_all b
        WHERE
            a.customer_trx_id = b.customer_trx_id
            AND a.trx_number = '167694'
            AND a.org_id = 143;

    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Transaction Number does not exist' );
    END;

    dbms_output.put_line('l_first_char-' || l_first_char);
    dbms_output.put_line('l_second_char-' || l_second_char);
    BEGIN
        SELECT
            receivables_trx_id
        INTO l_receivables_trx_id
        FROM
            ar_receivables_trx_all
        WHERE
            name LIKE '%'
                      || l_first_char
                      || '%'
                      || l_second_char
                      || '%';-- = 'CL.5Q Admin Fee USD- AGDF';--'Adjustment-Data Migration';
  --CL.5Q Admin Fee CLP- AGDF

    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Receivable Transaction  does not exist' || 'Adjustment-Data Migration');
    END;

    dbms_output.put_line('l_receivables_trx_id-' || l_receivables_trx_id);

/*------------------------------------+
| Setting global initialization |
+------------------------------------*/
    mo_global.init('AR');
    mo_global.set_policy_context('S','143');
    fnd_global.apps_initialize(12658,50731,222,0);
/*------------------------------------+
| Setting value to input parameters |
+------------------------------------*/
--Populate v_adj_rec record
    v_adj_rec.customer_trx_id := l_customer_trx_id;
    v_adj_rec.type := 'LINE';
    v_adj_rec.payment_schedule_id := l_payment_schedule_id;
    v_adj_rec.receivables_trx_id := l_receivables_trx_id;
    v_adj_rec.associated_cash_receipt_id := 9643009;--9253005;--cash_receipt_id from AR_CASH_RECEIPTS
    v_adj_rec.associated_application_id := 18118738;--16400596;-- RECEIVABLE_APPLICATION_ID from AR_RECEIVABLE_APPLICATIONS_V
    v_adj_rec.amount :=300;--c1.AMOUNT;
    v_adj_rec.apply_date := SYSDATE;--TO_DATE (c1.apply_date);
    v_adj_rec.gl_date := SYSDATE;--TO_DATE (c1.gl_date);
    v_adj_rec.created_from := 'ADJ-API';
 
 ar_adjust_pub.create_adjustment
    ('AR_ADJUST_PUB',1.0,
    v_init_msg_list,
    v_commit_flag,
    v_validation_level,
    v_msg_count,
    v_msg_data
    ,v_return_status
    ,v_adj_rec,
    v_chk_approval_limits,
    v_check_amount,
    v_move_deferred_tax,
    v_new_adjust_number,
    v_new_adjust_id,
    v_called_from,
    v_old_adjust_id
    );

    dbms_output.put_line('v_msg_count-' || v_msg_count);
    dbms_output.put_line('v_msg_data-' || v_msg_data);
    dbms_output.put_line('v_new_adjust_number-' || v_new_adjust_number);
    dbms_output.put_line('v_new_adjust_id-' || v_new_adjust_id);
    dbms_output.put_line('v_init_msg_list-' || v_init_msg_list);
    dbms_output.put_line('Successfully Completed-' || v_return_status);

    FOR i IN 1..v_msg_count LOOP
        dbms_output.put_line(i
                               || '. '
                               || substr(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,255) );
    END LOOP;

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        FOR i IN 1..v_msg_count LOOP
            dbms_output.put_line(v_msg_data);
            dbms_output.put_line(v_new_adjust_number);
            dbms_output.put_line(v_new_adjust_id);
            dbms_output.put_line(v_init_msg_list);
        END LOOP;
END;
/



Query to find out Adjustment amount
-----------------------------------------------------
select nvl(sum(amount),0)
into v_adjustments_amt
FROM
    ar_adjustments_v
    where customer_trx_id = p_invoice_id;
------------------------------------------------------

FNDLOAD Script for DFF

--Download
FNDLOAD apps/pwd 0 Y DOWNLOAD @FND:patch/115/import/afffload.lct okl_k_lines_dff.ldt DESC_FLEX APPLICATION_SHORT_NAME="OKL" DESCRIPTIVE_FLEXFIELD_NAME=OKL_K_LINES_DF


--Upload
FNDLOAD apps/pwd 0 Y UPLOAD @FND:patch/115/import/afffload.lct okl_k_lines_dff.ldt

fdfcmp apps/pwd 0 Y D DESC_FLEX APPLICATION_SHORT_NAME="OKL" OKL_K_LINES_DF

FNDLOAD Script for Request Set

--Download
FNDLOAD apps/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct DAILY_AUTOMATION_SET2.ldt REQ_SET REQUEST_SET_NAME='DAILY_AUTOMATION_SET2'

FNDLOAD apps/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct DAILY_AUTOMATION_LNK.ldt REQ_SET_LINKS REQUEST_SET_NAME='DAILY_AUTOMATION_SET2'


--Upload
FNDLOAD apps/pwd 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  DAILY_AUTOMATION_SET2.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD apps/holiday5 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  DAILY_AUTOMATION_LNK.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Script for Concurrent Program

--Download
FNDLOAD apps/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct VAT_INVOICE_PRINTING.ldt PROGRAM APPLICATION_SHORT_NAME="AR" CONCURRENT_PROGRAM_NAME="VAT_INVOICE_PRINTING"


--Upload
FNDLOAD apps/pwd 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct VAT_INVOICE_PRINTING.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Script for Value Set

--Download
$FND_TOP/bin/FNDLOAD apps/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct CONTRACT_NUMBER.ldt VALUE_SET FLEX_VALUE_SET_NAME="CONTRACT_NUMBER"


--Upload
$FND_TOP/bin/FNDLOAD apps/pwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct CONTRACT_NUMBER.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE